CODEWITHSUNDEEP
mysqljoin
user3564392
user3564392

Reputation: 25

SQL Updating multiple rows in one join

I have the following tables

old_id  | category_id
---------------------
45      | 3455
45      | 07072
38      | 0293

old_id  | new_id
----------------
45      | 1023
38      | 1027

In the first table the primary key is composite (old_id, category_id). There may be multiple categories for each old_id as you can see from old_id=45.

How do I update every old_id row with the relevant new_id?

I'm using MySQL 5.5

Upvotes: 0

Views: 45

Answers (5)

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7678

SQLFiddle

UPDATE tab1 T1 SET T1.old_id = (
  SELECT T2.new_id FROM tab2 T2 
  WHERE t2.old_id = t1.old_id
);

Then you will get tab1 output as

| OLD_ID | CATEGORY_ID |
|--------|-------------|
|   1023 |        3455 |
|   1023 |       07072 |
|   1027 |        0293 |

Upvotes: 0

Lucas Henrique
Lucas Henrique

Reputation: 1364

would this

UPDATE Table1 a 
    JOIN Table2 b ON a.old_id = b.old_id
    SET a.category_id= b.new_id

Upvotes: 0

Ilesh Patel
Ilesh Patel

Reputation: 2155

Try this

UPDATE TABLE1 JOIN TABLE2 ON 
    TABLE1.old_id = TABLE2.old_id 
    SET TABLE1.old_id = TABLE2.new_id

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

You can join both tables in an UPDATE query.

Try this:

UPDATE TableOld a 
    JOIN TableNew b ON a.old_id = b.old_id
SET a.old_id= b.new_id

Upvotes: 1

tbruyelle
tbruyelle

Reputation: 13045

Something like that :

update t1 
set old_id = new_id
from t2
where t1.old_id = t2.old_id

Upvotes: 1

Related Questions