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

Question

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)

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

You can update it by an UPDATE with an INNER JOIN:

UPDATE `table1` as a
INNER join table2 b on a.columnA = b.columnC
SET a.`columnB` = b.`columnD`
-- WHERE clause if needed ...

See: SQL Server - inner join when updating

edited May 23 '17 at 10:28

Community

1
1

answered Feb 05 '16 at 07:56

leonard.javiniar

539
8
25

I have updated the code for MySQL. Can you try it again? – leonard.javiniar Feb 05 '16 at 08:24
I missed the alias `a` and `b`. Here's an update: http://pastie.org/10709664. Are those tables and columns above do exists in your database? – leonard.javiniar Feb 05 '16 at 08:42

score -1 · Answer 2 · answered Feb 05 '16 at 07:56

-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

answered Feb 05 '16 at 07:56

Jimish Gamit

1,014
5
15

This is a long winded way of writing an inner join – Strawberry Feb 05 '16 at 08:38

score -1 · Answer 3 · answered Feb 05 '16 at 07:56

-1

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.

answered Feb 05 '16 at 07:56

Mateusz Walas

1
2

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

3 Answers3