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?

Answer 1

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

Answer 2

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