SQL Update after Joining Two Tables

Question

I am new to SQL, using Microsoft SQL Server Management Studio.

I am trying to write a SQL statement that performs an update after two tables are joined.

I have two tables: myTable1 and myTable2. Both share a field MyID, which is going to be the field that I join on. myTable1 contains a column called BitToUpdate. And MyTable2 contains a column called BitToCheck.

I want to set BitToUpdate in myTable1 to be 1 where BitToCheck in myTable2 is 1 as well.

Here is what I have:

SELECT M.MyID, BitToUpdate, BitToCheck
INTO #temp_table
FROM myTable1 as T1
LEFT JOIN myTable2 as T2
ON M.MyId = PO.MyId

So first I tried to join the two tables myTable1 and myTable2 on their IDs, and store the result in a temporary table.

Next, I want to update BitToUpdate to be 1 where BitToCheck is 1.

So to do that in the temporary table, I have:

UPDATE #temp_table
SET 
    `BitToUpdate` = 1
WHERE
    `BitToCheck` = 1

This updates the BitToUpdate successfully in #temp_table. However, when I do a select on myTable1, I find that BitToUpdate is not changed. I suppose this makes sense as #temp_table isn't really a "pointer"....

But what would be the correct way to approach this join and update?

score 36 · Accepted Answer · answered Mar 18 '13 at 16:17

36

You don't need to use a LEFT JOIN here, since you are checking on a condition from table 2, so an INNER JOIN should be better here.

UPDATE T1
SET T1.BitToUpdate = 1
FROM myTable1 T1
INNER JOIN myTable2 T2
    ON T1.MyId = T2.MyId
WHERE T2.BitToCheck = 1

answered Mar 18 '13 at 16:17

Lamak

69,480
12
108
116

This is wrong, please refer to this http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query The order are:Update --> JOIN --> SET – Hendyanto Nov 18 '15 at 04:30
5

In case someone cares: the query above is not wrong. In MS SQL you can do either UPDATE --> JOIN --> SET or UPDATE --> SET --> FROM --> JOIN ... or even UPDATE --> SET --> FROM --> WHERE... – Alex X. Oct 20 '17 at 13:53

score 4 · Answer 2 · answered Mar 18 '13 at 16:14

What you are doing in your first query is updating a temp table named #temp. the updates never go to the actual table myTable1 or mayTable2. To update records while joining with other tables try this:

UPDATE T1
SET T1.BitToUpdate = 1
FROM myTable1 as T1
LEFT JOIN myTable2 as T2 (ON T1.MyId = T2.MyId)
WHERE T2.BitToCheck = 1

score 1 · Answer 3 · answered Mar 18 '13 at 16:15

1

--SELECT M.MyID, BitToUpdate, BitToCheck
--INTO #temp_table
update t1
  set t1.BitToUpdate = 1
FROM myTable1 as T1
LEFT JOIN myTable2 as T2
ON t1.MyId = t2.MyId
where t2.bittocheck = 1

answered Mar 18 '13 at 16:15

Jimbo

2,529
19
22

score 0 · Answer 4 · answered Mar 18 '13 at 16:16

0

UPDATE T1
SET BitToUpdate=1
FROM myTable1 T1
LEFT JOIN myTable2 T2
ON T1.MyId=T2.MyId
WHERE T2.BitToCheck=1

answered Mar 18 '13 at 16:16

rbedger

1,177
9
20

SQL Update after Joining Two Tables

4 Answers4

Linked