UPDATE not working properly

Transact-SQL
1

I'm having an issue with updating a table. There are 610 records which should be updated, but only 73 are "affected".

Here's the script I'm running:

update PFaids2005.dbo.work_study_tracking

set dept_fws=e.dept_fws,
supr_fws=e.supr_fws,
telephone_fws=e.telephone_fws,
email_fws=e.email_fws,
dept_sws=e.dept_sws,
supr_sws=e.supr_sws,
telephone_sws=e.telephone_sws,
email_sws=e.email_sws,
dept_iws=e.dept_iws,
supr_iws=e.supr_iws,
telephone_iws=e.telephone_iws,
email_iws=e.email_iws,
dept_ows=e.dept_ows,
supr_ows=e.supr_ows,
telephone_ows=e.telephone_ows,
email_ows=e.email_ows

from Manual.dbo.FWSP_Employment_Roll as e
where PFaids2005.dbo.work_study_tracking.stu_ay_token=e.stu_ay_token

The table "as e" table contains 610 records, but only 73 records of the table I'm updating are getting changed. HELP?

2

Only 73 rows match this condition.

3

That would make sense, right? Except I know that all of the "e" tokens are also in the other table. I've manually hunted about 100 of them just to verify.

4

What do you get from:

SELECT *
FROM PFaids2005.dbo.work_study_tracking
INNER JOIN Manual.dbo.FWSP_Employment_Roll AS e
	ON PFaids2005.dbo.work_study_tracking.stu_ay_token = e.stu_ay_token
1 Like
5

74 records!

6

there's your answer.

1 Like
7

Ok, so the logistics proves that I am crazy! Now I have to go figure out why those records don't exist!

8

Ok, so new question: if the records don't exist in the table, can my UPDATE statement create them?

9

UPDATE cannot insert rows. Either use UPDATE followed by INSERT ... WHERE NOT EXISTS(...) or use a MERGE statement.

1 Like