July 30, 2010 at 5:30 am
Hi,
I have a table with 180,000 rows, i need to update 7645 of those rows but i can only determine what those rows are by using a select join query.
I tried using and example of a select update where something exists but this seems to update the entire 180,000 rows not just the 7645 i need. Please see below if you can see where i am going wrong.
This query only pulls 7645 rows, the user1 field in the wce_contact table i need to update with a value
SELECT h.NOTES, C.STATUS, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID LEFT OUTER JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status LIKE '%bath%')
This is the query i thought i could use to update the fields in the wce_contact table for those records based on the above query.
However this query updates every row in the database... What am i doing wrong? Thanks for looking.
SELECT h.NOTES, C.STATUS, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID LEFT OUTER JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status LIKE '%bath%')
July 30, 2010 at 5:38 am
Please post the UPDATE query that you are using. I can see only 2 SELECT queries.
And one more thing, there is no use of the LEFT OUTER JOIN. The Where Clause in your query makes it an INNER JOIN.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 30, 2010 at 6:17 am
Sorry, i copied the wrong query, this is the update query.
UPDATE wce_contact
SET user1 = 'prospect Bath'
WHERE EXISTS
(SELECT h.NOTES, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID LEFT OUTER JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status = 'Prospect Bath'));
July 30, 2010 at 6:37 am
Your exists query does not reference the outer table (the table being updated) at all. Sure, it mentions wce_contact, but since it joins that table in and gives it an alias, it's not assumed to be the same table as is being updated. Hence the subquery can be evaluated once (it's independent of the outer table) and determined to be true or false. In this case, since it obviously returns records, your update reduces to
Update Table...
where true
Hence every row gets updated.
I'm going to assume that instead of joining wce_contact into the subquery you really want to link to the table being updated. If so...
UPDATE wce_contact
SET user1 = 'prospect Bath'
WHERE EXISTS (
SELECT 1
FROM wce_history AS h
INNER JOIN wce_linkto AS l ON h.UNIQUEID = l.LUniqueID
WHERE l.LEntityID = wce_contact.UNIQUEID -- correlation to outer table
AND wce_contact.Status = 'Prospect Bath');
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 6:37 am
sc-w (7/30/2010)
Sorry, i copied the wrong query, this is the update query.
UPDATE wce_contact
SET user1 = 'prospect Bath'
WHERE EXISTS
(SELECT h.NOTES, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID LEFT OUTER JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status = 'Prospect Bath'));
Your query just checks if the sub-query returns any row. If it returns even 1 row, the whole table will be updated.
Try the below query
UPDATEwce_contact
SETuser1 = 'prospect Bath'
WHERE EXISTS (
SELECTh.NOTES, c.user1
FROMwce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID LEFT OUTER JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status = 'Prospect Bath')
-- Add the below condition
ANDc.user1 = wce_contact.user1
);
Just saw that Gail had already given a good explanation while i was typing the answer.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 30, 2010 at 7:43 am
Thanks for both the replies, i kind of see what your saying but if i try either of the following i still get all my rows updated. not jsut the 7645. Am i missing something? Sorry if i am.
UPDATE wce_contact
SET user1 = 'xxxxXx'
WHERE EXISTS (
SELECT h.NOTES, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID left outer JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status = 'Prospect Bath')
-- Add the below condition
AND c.user1 = wce_contact.user1
or
UPDATE wce_contact
SET user1 = 'xxxxXx'
WHERE EXISTS (
SELECT h.NOTES, c.user1
FROM wce_history AS h INNER JOIN
wce_linkto AS l ON h.UNIQUEID = l.LUniqueID inner JOIN
wce_contact AS c ON l.LEntityID = c.UNIQUEID
WHERE (c.Status = 'Prospect Bath')
-- Add the below condition
--AND c.user1 = wce_contact.user1
July 30, 2010 at 8:35 am
How about the query I wrote for you? It's different to either of the ones listed there...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 9:36 am
Thanks Gail, i over looked all the detail and thought it was the same... Anyway worked perfectly, thanks for teaching me that.
July 30, 2010 at 9:52 am
This may be of use...
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply