Need help for update not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help for update not working

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-09-07 : 13:37:49

I have an update that does not seem to be updating the records correctly. Need some help. Here is the update statement.

UPDATE LTFLM
  SET DATE_CREATED = LOT.DATE_CREATED
  FROM LOTDATES LOT
  WHERE EXISTS (SELECT 1 FROM LOTDATES WHERE (PART_ID = LTFLM.PART_ID) AND (LOT_ID = LTFLM.LOT_ID))

Here are the results from the following two select statements:

SELECT PART_ID, LOT_ID, DATE_CREATED FROM LTFLM 
  WHERE PART_ID = '280-001215'

PART_ID                 	LOT_ID	                DATE_CREATED
280-001215               	11332           	2008-08-28 00:00:00.000
280-001215               	11950           	2008-12-31 00:00:00.000
280-001215               	7691            	2008-12-31 00:00:00.000
280-001215               	819             	2008-12-31 00:00:00.000
280-001215               	B5263/B5320     	2009-12-16 12:50:51.987

and

SELECT * FROM LOTDATES
  WHERE EXISTS (SELECT 1 FROM LTFLM WHERE LOT_ID = LOTDATES.LOT_ID)
  AND PART_ID = '280-001215'

Part_id	        lot_id	Date_Created
280-001215	819	2002-12-31 00:00:00.000
280-001215	7691	2008-12-31 00:00:00.000
280-001215	11950	2009-12-31 00:00:00.000

It looks to me like the values for the Lot_ID 819 should be set to 2002-12-31. But I don't see that happening. Any help would be greatly appreciated. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 13:45:54

Try this:

UPDATE LTFLM
SET DATE_CREATED = LOT.DATE_CREATED
FROM LTFLM
JOIN LOTDATES LOT
ON LOT.PART_ID = LTFLM.PART_ID AND LOT.LOT_ID = LTFLM.LOT_ID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

texassynergy
Starting Member

26 Posts

Posted - 2010-09-07 : 13:57:22

Ok Tara. You got me.

Yours works and mine doesn't. Why the difference? What about my code causes it to not update correctly?

And thanks very much for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:05:02

I think the problem is with the FROM portion. It should be FROM LTFLM and not LOTDATES. But the JOIN method is preferred anyway.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-09-07 : 14:12:15

You've got no relationship between the two tables


UPDATE LTFLM
  SET ...
  FROM LOTDATES LOT

Actually: I'm surprised that you don't get either an error or a Cartesian join ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:20:53

By the way, use SELECT * and not SELECT 1 when using EXISTS/NOT EXISTS. * is actually faster in this case.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

texassynergy
Starting Member

26 Posts

Posted - 2010-09-07 : 14:24:00

Thanks for the information. I am surprised as well. Both updates stated that they updated 4011 records. The first one didn't update the values correctly and Taras did. But I do get your point Kristen.

Thanks again. I will try to analyze my and be more careful in the way I create my statements. This site has been very helpful and it is because of the experience and knowledge of people like you, Tara and others. I greatly appreciate it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:26:07

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-09-08 : 02:21:57

"But I do get your point Kristen."

Sorry, wasn't meaning to labour the point! just to point out that you needed a relationship in the FROM (or, I think that you do - I'm still surprised it didn;t give error.

FWIW we always use an Alias for the Update Table, even if there is only one table (then consistent even if a JOIN is added in the future), rather than using the Table's name in the UPDATE clause. Usually we use "U" for the table to be updated:


UPDATE U
SET ...
FROM Table1 AS U
WHERE ...

UPDATE U
SET ...
FROM Table1 AS T1
     JOIN Table2 AS U
         ON U.ID = T1.ID
WHERE ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources