How to update multiple matches by SQL?

Question

I have data from tableA (columns A1 and A2) that I want to update in tableB (columns B1 and B2) when A1 matches B1. So A2 value will be updated in B2 when A1 and B1 match

The code below works but only for the first A1 and B2 match. In my database there are several matches and I would like to have all B2 values updated

$sql = "UPDATE tableB INNER JOIN tableA
set tableB.B2 = tableA.A2
where tableB.B1 = tableA.A1";

Any idea how to have all tableB.B2 values updated each time tableB.B1 = tableA.A1?

This should do what you want, but the `where` clause should be an `on` clause. — Gordon Linoff, Apr 16 '19 at 12:18
Can you try: `UPDATE tableB set B2 = tableA.A2 FROM tableA INNER JOIN tableB ON tableB.B1 = tableA.A1` — Richard Hansell, Apr 16 '19 at 12:25

BlackSwan · Answer 1 · 2019-04-16T12:39:10.333

0

$sql = "UPDATE tableB INNER JOIN tableA ON tableB.B1 = tableA.A1
set tableB.B2 = tableA.A2";

Check this link for further details: Update a table using JOIN in SQL Server?

edited Apr 16 '19 at 12:39

answered Apr 16 '19 at 12:26

BlackSwan

374
2
8

How to update multiple matches by SQL?

1 Answers1