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
– ôkio, Commented Jan 6, 2016 at 9:01
I was trying to to find the inner and the update - didn't worked. received error 1064 - something wrong with the syntax — Katy
– Katy, Commented Jan 6, 2016 at 9:05
well then you have to list the column you want to update, like in Code-Monk's answer. — ôkio
– ôkio, Commented Jan 6, 2016 at 9:09

Abhishek · Accepted Answer · 2016-01-06 09:08:15Z

20

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 6, 2016 at 9:08

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Katy Over a year ago

i have in both tables around 120 columns . is there a way to do this all together?

Abhishek Over a year ago

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 .

Gubberrr Over a year ago

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...

Archana · Accepted Answer · 2016-01-06 09:33:34Z

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 6, 2016 at 9:33

answered Jan 6, 2016 at 9:06

Archana

3892 gold badges5 silver badges17 bronze badges

Collectives™ on Stack Overflow

MYSQL - UPDATE multiple rows from another table

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related