How to update one table with data from the other, based on condition from the first

Issue

I have:
enter image description here

The goal is to update table A with groupID from the table B, with the rule:

  • if A._groupID = 1, get first B.groupID of the same subjectID and write the value to A.groupID
  • if A._groupID = 2, get second B.groupID of the same subjectID and write the value to A.groupID

(Explanation: in table A groups were numbered from 1-20 for each subject, but with app upgrade it is necessary to store group data to another table and assign them a proper primary key ID (b.groupID), because of that this pairing is needed).

What I tried:

UPDATE A
    JOIN    B
        ON  B.subjectID = A.subjectID
SET A.groupID = B.groupID
ORDER BY A.groupID ASC

This results with (always writes the first result from table B):

enter image description here

Efficiency is not a concern because this query will be executed only once..

How to make this query work?

Can you please advise on how to modify the update query to fix the error?

Solution

If you can use analytic functions (Mysql 8) this is a breeze with row_number()

This works by first creating a derived table for tableB (another CTE could also be used). This then uses row_number to add numbers (in this case just 1 or 2) according to the ordering of groupId which matches the _groupId in tableA. Then it’s just a simple join on both columns to assign the right value.

update a 
join (
  select *,
    row_number() over(partition by subjectid order by groupid) _groupid
  from b
)b on a.subjectid=b.subjectid and a._groupid=b._groupid
set a.groupid=b.groupid

working Fiddle

Answered By – Stu

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published