Question 247741 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to update newly added column to back fill the data for old records

I have a table vehicle with columns id, model, colour, is_default.
in this is_default is a newly added column and we have 4000 old recored before adding is_primary.

the default value for is_primary is 0.

I want to fill this column for all the present records in such a way that

  1. A model can have multiple colours, but can only have one colour as is_default = 1. All others will be false (0).
  2. If a model already has a default colour then the query should not touch any records related to that model

Note: We have 1000 models and 50 colours possible

I tried below solution

UPDATE
vehicle AS scl
JOIN (
    SELECT model, MIN(id) AS min_id
    FROM vehicle 
    WHERE is_default = 0 
    GROUP BY model) AS sc 
ON scl.model sc2.model AND scl.id= sc2.min_id 
SET scl.is_default = 1;

but when I execute this it updates records which already have a default colour.

Tags:

2

Answers


  1. 0

    Your WHERE clause isn’t excluding the entire model when is_default = 1, it’s just ignoring those rows when finding MIN(id) for the model. Use a subquery to exclude all the rows with that model.

    UPDATE
vehicle AS scl
JOIN (
    SELECT model, MIN(id) AS min_id
    FROM vehicle AS v
    WHERE NOT EXISTS (
        SELECT 1
        FROM vehicle AS v1
        WHERE v1.model = v.model AND v1.is_default = 1
    )
    GROUP BY model) AS sc 
ON scl.model sc2.model AND scl.id= sc2.min_id 
SET scl.is_default = 1;
    Login or Signup to reply.
  2. 0

    This will do it:

    UPDATE Vehicle v
INNER JOIN (
  SELECT ID, CASE WHEN row_number() over (partition by model order by case when is_default = 1 then 0 else 1 end, colour) = 1 THEN 1 ELSE 0 END New_Default
  FROM Vehicle
) p ON p.id = v.id
SET IS_DEFAULT = New_Default

    See it here:

    https://dbfiddle.uk/pq-rMtap

    However, it will set every row… just to the same existing value in cases where that is desired. The trick there is using the ORDER BY within the window to sort the current default first.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search