'Update MySQL column from another table column only if the autoincrement data is equal

I have a database with 2 tables. In table1 there are two column:

columnA (autoincrement) and columnB (text)

And in table2: columnC (autoincrement) and columnD (text)

I want to replace (copy) data in columnB with data from columnD only if the autoincrement columns are equals.

I tried with this, but its not correct:

UPDATE table1 
   SET columnB = (
      SELECT columnD 
      FROM table2 WHERE (
         SELECT columnA FROM table1 = SELECT columnC FROM table2)
mysqlsql


Solution 1:[1]

You can use left join: UPDATE table1 t1 left join table2 t2 on t1.columnA=t2.columnC set t1.columnB=t2.columnD where t2.columnc is not null

Solution 2:[2]

You are trying to update columnB with one query, which means regardless of whether columnA and columnC are equal. I'd suggest creating a procedure, implementing the if statement there and then simply calling the procedure.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jimish Gamit
Solution 2 Mateusz Walas

Related Questions