Strange Results from UPDATE Query.

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 2005 Forums

Transact-SQL (2005)

Strange Results from UPDATE Query.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2010-02-15 : 11:34:28

Hi All,
I created a SQL SELECT query that garners around 5200 results from my DB when I run it. It looks like this:

select * from contact1 LEFT JOIN CONTACT2 ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO where CREATEON='2010-02-08 00:00:00.000' AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY')

I then created a SQL UPDATE query that utilizes a WHERE EXISTS (sql query) format to update those 5200 results. The query is below:

UPDATE CONTACT2 
SET UPREAWDAMT='', UPREAWDCEL='10' + UPREAWDCEL
WHERE EXISTS (select * from contact1 LEFT JOIN CONTACT2 ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO where CREATEON='2010-02-08 00:00:00.000' AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY'))

Essentially, in any row that is found in the subquery it should blank the column UPREAWDAMT and prefox a 10 to the column value of UPREAWDCEL. When I run the update query it updates all the rows in contact2 - not just the 5200 the select query by itself garners. Can anyone help me understand why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:38:45

WHERE EXISTS looks for boolean result. so far as select query inside returns any resultset it will return true. Also note that inside exists query you're not linking it to outside table. Can you try small modification below.


UPDATE c3
SET UPREAWDAMT='', UPREAWDCEL='10' + UPREAWDCEL
FROM CONTACT2 c3
WHERE EXISTS (select * from contact1 c1 LEFT JOIN CONTACT2 c2 ON c1.ACCOUNTNO=c2.ACCOUNTNO 
where CREATEON='2010-02-08 00:00:00.000' 
AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY')
AND ACCOUNTNO = c3.ACCOUNTNO)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-15 : 11:42:53

Bascially what you have done is taken a query that generates one or more results (5200) and put it in the exists clause. So for every row in Contact2 there EXISTS a row that results from the LEFT JOIN in the EXISTS clause.

In order to fix the query, you need to remove the LEFT JOIN and use a corrilated query. For example:

UPDATE 
	CONTACT2 
SET 
	UPREAWDAMT='', 
	UPREAWDCEL='10' + UPREAWDCEL
WHERE EXISTS (select * from contact1ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO where CREATEON='2010-02-08 00:00:00.000' AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY'))

or as a join

UPDATE 
	C2
SET 
	UPREAWDAMT='', 
	UPREAWDCEL='10' + UPREAWDCEL
from 
	contact1 
LEFT JOIN 
	CONTACT2  AS C2
	ON CONTACT1.ACCOUNTNO=C2.ACCOUNTNO 
where 
	CREATEON='2010-02-08 00:00:00.000' 
	AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY')

Additionally, SQL 2005 and up have a MERGE statement that also does this and won't update the same row more than once.

davidshq
Posting Yak Master

119 Posts

Posted - 2010-02-15 : 11:50:04

@visakh - that query you provided seems to run forever - gets stuck in a loop somewhere and never ends.
@lamprey - thanks for the suggestions, i'm looking into them now.
Dvae.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:53:39

quote:
Originally posted by davidshq

@visakh - that query you provided seems to run forever - gets stuck in a loop somewhere and never ends.
@lamprey - thanks for the suggestions, i'm looking into them now.
Dvae.

Actually you dont need extra join inside so that it boils down to Lampreys first suggestion. So you can try that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

davidshq
Posting Yak Master

119 Posts

Posted - 2010-02-15 : 11:57:06

Hmmm...I get an error when using Lamprey's first query:
"Incorrect syntax near the keyword 'ON'."
Line reported is:
WHERE EXISTS (select * from contact1 ON CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO where CREATEON='2010-02-08 00:00:00.000' AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY'))
Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:11:34

Actually that should be


UPDATE 
	CONTACT2 
SET 
	UPREAWDAMT='', 
	UPREAWDCEL='10' + UPREAWDCEL
WHERE EXISTS (select * from contact1  where CREATEON='2010-02-08 00:00:00.000' AND (CREATEBY='MASTER' OR CREATEBY='DMACKEY') AND  CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

davidshq
Posting Yak Master

119 Posts

Posted - 2010-02-15 : 13:31:32

Hmmm...Still seems to be a problem. Running 10 mins. + and the query hasn't completed.
Dave.

davidshq
Posting Yak Master

119 Posts

Posted - 2010-02-15 : 13:57:41

Never mind. It worked. Thanks for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 00:57:07

what all indexes you've on table? have you had a look at execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources