How to copy values corresponding to the foreign key from another table to this table?

Question

I have two tables tblA and tblX. In tblA, there is a foreign key FKX from tblX. I want to copy corresponding values of StringColumn to strColCopy column. It introduces redundancy but it's part of longer migrations process.

How can I access tblX.StringColumn cell for every single tblA.FKX cell?

Which dbms are you using?

jarlh
– jarlh

2018-03-30 09:24:58 +00:00
Commented Mar 30, 2018 at 9:24 — jarlh
– jarlh, Commented Mar 30, 2018 at 9:24

marc_s · Accepted Answer · 2018-03-30 10:55:16Z

You can use UPDATE using JOIN

Solution for SQL Server:

UPDATE A SET A.strCol = X.StringColumn FROM TblA A JOIN (SELECT FKX, strCol FROM TxlX GROUP BY FKX, strCol) AS X ON A.FKX = X.ID

Syntax for general Update join for SQL Server:

UPDATE a SET a.columnToUpdate = [something] FROM TABLEA a JOIN TABLEB b ON a.colA = b.colB

Solution for MySQL:

UPDATE TblA A JOIN (SELECT FKX ,strCol FROM TxlX GROUP BY FKX ,strCol)AS X ON A.FKX = X.ID SET A.strCol = X.StringColumn

Syntax for general Update join for MySQL:

UPDATE TABLEA a JOIN TABLEB b ON a.colA = b.colB SET a.columnToUpdate = [something]

Collectives™ on Stack Overflow

How to copy values corresponding to the foreign key from another table to this table?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related