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

Issue

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?

• the subjects also have multiple types (like lecture, exercise, practice), so group counting should take a Type into a consideration when counting.
• fiddle with working example without a Type consideration: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=172109270f3f83f8e763f7d4daffb26f
  Result:
  

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