SQL: Updating values only if matching between two tables

3927 views mysql
2

I have two tables that both have the columns personID, checkedIn. Table 1 and Table 2 contain shared data but also hold unique personID data.

For each row in Table 1, I wish to update the checkedIn column to the same column in Table 2 for each matching personIDs. If the personId does not exist in Table2, the row is left alone.

I have tried updating but am getting all rows of null due to the personIDs not finding a match.

answered question

Please post some sample data and expected output with your trials.

2 Answers

5

You will need to use a query like below:

UPDATE Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.personID  = t1.personID 
SET t1.checkedIn = t2.checkedIn

SQL Server Solution:

update t
set t1.CheckedIn = t2.CheckedIn
from Table1 t1
inner join t2 on t2.PersonID = t1.PersonID

posted this
10

You can use multi-table update for this:

UPDATE
  table1 t1 INNER JOIN table2 t2 ON t1.personID = t2.personID
SET t1.checkedIn = t2.checkedIn;

This will only update the rows with a matching personID because of the INNER JOIN.

posted this

Have an answer?

JD

Please login first before posting an answer.