SELECT Exists question - SQL Server Forums

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)

SELECT Exists question

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-09-20 : 05:57:39

Hi,

I have just ran an update query that went wrong, luckily it was on an un-important field but i would like to know why i didn't get the results i expected.

This select query give me 6300 rows and for these rows i want to update field "user1" to the vaule of "Hold"


SELECT C.STATUS, H.SUBJECT, c.user1  FROM WCE_HISTORY H INNER JOIN WCE_LINKTO L ON H.UNIQUEID = L.LUNIQUEID INNER JOIN WCE_CONTACT C ON L.LENTITYID = C.UNIQUEID WHERE c.status = 'Prospect1' and H.SUBJECT LIKE '%Web meeting Invitation%' order by c.status

So i figured that if i tried the exists query it would look at the select rows and just do the 6300, it didn't it updated my entire database 240,000 rows so they all had teh value of "hold" in the "user1" field.


UPDATE wce_contact  
SET user1 = 'Hold'
WHERE EXISTS (
  SELECT C.STATUS, H.SUBJECT, c.user1  FROM WCE_HISTORY H INNER JOIN WCE_LINKTO L ON H.UNIQUEID = L.LUNIQUEID INNER JOIN WCE_CONTACT C ON L.LENTITYID = C.UNIQUEID WHERE c.status = 'Prospect1' and H.SUBJECT LIKE '%Web meeting Invitation%');

Any advise would be great, thanks.

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 06:30:28

Try this



UPDATE C
SET user1 = 'Hold'
FROM wce_contact C
WHERE EXISTS (
  SELECT C.STATUS, H.SUBJECT, c.user1  FROM WCE_HISTORY H
   INNER JOIN WCE_LINKTO L ON H.UNIQUEID = L.LUNIQUEID
 INNER JOIN WCE_CONTACT C ON L.LENTITYID = C.UNIQUEID  
  WHERE L.LENTITYID=C.UNIQUEID  and c.status = 'Prospect1' 
 and H.SUBJECT LIKE '%Web meeting Invitation%');

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-09-20 : 06:42:20

Thanks, that got it, i see it was becuase i included the contact table in and that makes it see all rows.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

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

Isn't it easier to write this


UPDATE c
SET
	[user1] = 'Hold'
FROM
	wce_contact AS c
	JOIN wce_linkto AS l ON l.[LENTITYID] = c.[UNIQUEID]
	JOIN wce_history AS h ON h.[UNIQUEID] = l.[UNIQUEID]
WHERE
	c.status = 'Prospect1'
	AND h.[subject] LIKE '%Web meeting Invitation%'

Don't think you need the exists at all.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

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

If multiple rows in the JOINed tables then the update will occur "multiple times", which is maybe inefficient? (Dunno what SQL does in such circumstances, at worst it will update the same row multiple times with the same value, at best it has to do some sort of DeDupe?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources