CODEWITHSUNDEEP
mysqljoinsql-update
Samuel
Samuel

Reputation: 25

MySQL - update whole column with records from parent table?

I have two tables t1, t2 that I have created and loaded data from a CSV into these.

I had to then create a new PK column as the existing columns (t1.old_id, t2.old_id) are strings that would naturally be a PK are not absolutely fixed (this seems to be advised against?) so I created a id PK INT AUTO_INCREMENT in each table

as one record in t1 is linked to many in t2 and I want to maintain referential integrity between these two tables.

I believe what i need to do is create an id INT NOT NULL in t2 as an FK

This t2.id is blank at the moment (as it is dependent ont1.id`)

Am I right in thinking I need an UPDATE query with a JOIN of some description to make this work?

The following produces the data exactly that I want to update into my t2.id column - but I don't know how to do the update

select t1.id
from t1
inner join t2
on t1.old_id = t2.old_id

Upvotes: 1

Views: 46

Answers (2)

Alex
Alex

Reputation: 21766

You can use a join in your UPDATE statement like this: 

UPDATE t2 
    JOIN t1 ON t1.old_id = t2.old_id
SET t2.id = t1.id

Upvotes: 1

ughai
ughai

Reputation: 9890

You can use a correlated UPDATE query like this

UPDATE t2
SET id = (SELECT MAX(t1.id) FROM t1 WHERE t1.old_id = t2.old_id);

*Assuming you have a single t1.id for each t1.old_id

On a Separate Note, You should name t2.id like t2.t1ID so as to remove ambiguity if and when you have a identity column in t2 as well named id

Upvotes: 0

Related Questions