MYSQL - UPDATE multiple rows from another table

Question

I have 2 tables. one from yesterday (300k rows) and another one from today with the same count of rows but the data changes in some columns.

Those two tables have around 120 columns.

How can i update only the changes.
I have tried using delete :

   delete from tableA
   where id in (select id from tableB)

But it too slow.
Also tried

   update tableA inner join tableB
   on tableA.id=TableB.id

And it didn't worked.

What didn't work when you tried tableA inner join tableB on tableA.id=TableB.id ? — ôkio, Jan 06 '16 at 09:01
I was trying to to find the inner and the update - didn't worked. received error 1064 - something wrong with the syntax — Katy, Jan 06 '16 at 09:05
well then you have to list the column you want to update, like in Code-Monk's answer. — ôkio, Jan 06 '16 at 09:09

score 18 · Accepted Answer · answered Jan 06 '16 at 09:08

18

You have to set the values in your update query to get the changes.

Example:

update tableA inner join tableB on tableA.id=TableB.id
set tableA.col1=TableB.col1,
    tableA.col2=TableB.col2,
    tableA.col3=TableB.col3;

and also you can add more conditions in where clause to make query run on filtered records.

answered Jan 06 '16 at 09:08

Abhishek Ginani

i have in both tables around 120 columns . is there a way to do this all together? – Katy Jan 06 '16 at 09:14
yeah..but you have set only those columns want to get changes. If you want to update all column values, you can try other tools like `Excel` to prepare query . – Abhishek Ginani Jan 06 '16 at 09:16
If you get an error like "Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect." you can add `SET SQL_SAFE_UPDATES=0;` [more info...](https://stackoverflow.com/a/66529407/2587338) – Gubberrr Aug 04 '21 at 11:25

Archana · Answer 2 · 2016-01-06T09:33:34.780

0

delete from tableA where id in (select id from tableB)

Instead of above query try this:-

Delete tableA from tableA left Join tableB ON  tableA.id = tableB.id where tableB.id IS NOT NULL;

edited Jan 06 '16 at 09:33

answered Jan 06 '16 at 09:06

Archana

2 Answers2