Update only rows that does not match rows from another table

user_8202757 0
Viewed 920

I have 3 tables:

1) tblPerson

id      pin         name       dept_id       
---|-----------|------------|------------|
1  |    123    |    Lisa    |    100     |
2  |    234    |    Rob     |    200     |

2) tblDepartment

id     dept_name      
-----|-----------|
100  |    IT     | 
200  |    HR     |

3) tblMaster 

id     emplid       name        m_dept       
---|-----------|------------|------------|
1  |    123    |    Lisa    |     IT     |
2  |    234    |    Rob     |     HR     |

• tblDepartment gets its rows from existing departments present in tblMaster

• in tblPerson, dept_id is a foreign key from tblDepartment

I am trying to create a query that will update the dept_id in tblPerson for all rows where the m_dept does not match the dept_name of the dept_id linked to a person.

So for example, if in tblMaster, I change 'IT' under m_dept from row 1 to 'HR', running the query will change the dept_id of row 1 in tblPerson to '200'

SQL Query:

UPDATE [dbo].[tblPerson] 
SET dept_id = d.id
FROM [dbo].[tblMaster] m 
INNER JOIN [dbo].[tblPerson] p
ON p.pin = m.emplid
INNER JOIN [dbo].[tblDepartment] d
ON d.dept = m.m_dept

the query above will update all rows..when i try to add:

WHERE d.dept != m.m_dept

and I change the m_dept 'IT' to 'HR' of row 1 in tblMaster ..it does not update anything

need help please ;m;

tsql sql-server sql
1 Answers

You are currently using both d.dept = m.m_dept (in the JOIN condition) and d.dept != m.m_dept in the WHERE, so of course you are updating no rows.

You either use a EXISTS or NOT EXISTS, or another JOIN:

UPDATE P
SET P.dept_id = D1.id
FROM dbo.tblPerson P
INNER JOIN dbo.tblMaster M
    ON P.pin = M.emplid
INNER JOIN dbo.tblDepartment D1
    ON D1.dept_name = M.m_dept
INNER JOIN dbo.tblDepartment D2
    ON P.dept_id = D2.id
WHERE D1.dept_name <> D2.dept_name
;
user_5192160
Related