MySQL: How to update one table with data from the other, based on condition from the first-CodePudding

I have:

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):

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

How to make this query work? Thank you

CodePudding user response：

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