January 10, 2014 at 8:43 am
Hello-
I’m having a minor issue and I’m having trouble figuring out why. So basically all I want to do is update one column using the UPDATE and SELECT below. I’m expecting to update roughly 17,600 records which is what I get back when I run just the SELECT without the UPDATE. When I run the script with the UPDATE included I end up updating over 55,000 rows (in my test environment of course )
I think it may be something I’m doing wrong in the UPDATE part. Can someone please give me a hint what I’m doing wrong? UPDATE Productss
SET HotLeadParticipant=0 WHERE EXISTS(
SELECT DISTINCT
a.BoxID
,a.BoxName
,p.ProductsID
,p.ProductsName
,p.HotLeadParticipant
FROM
dbo.ValidProducts vp
INNER JOIN dbo.BoxProducts ap
ON vp.BoxProductID = ap.BoxProductID
INNER JOIN dbo.Boxs a
ON ap.BoxID = a.BoxID
INNER JOIN dbo.tblProducts p
ON vp.ProductID = p.ProductsD
WHERE a.BoxID IN (297,
318,
337,
354,
397,
398,
504,
535,
557,
604,
733)
January 10, 2014 at 9:00 am
i'm presuming that the code in your EXISTS statement always brings back at least one row?
it looks like, because it always brings back data, the EXISTS statement will always come back TRUE.
so your basically saying:
UPDATE Productss
SET HotLeadParticipant=0
WHERE
1 = 1 --this will always be true
that means your updating every record in your table..
January 10, 2014 at 9:03 am
Can you join Productss (is it really spelled with two S's :hehe:) to the select statement i.e.: UPDATE pdt
SET pdt.HotLeadParticipant=0
FROM
dbo.ValidProducts vp
INNER JOIN dbo.BoxProducts ap
ON vp.BoxProductID = ap.BoxProductID
INNER JOIN dbo.Boxs a
ON ap.BoxID = a.BoxID
INNER JOIN dbo.tblProducts p
ON vp.ProductID = p.ProductsD
INNER JOIN dbo.Productss pdt
ON vp.ProductID = pdt.ProductID
WHERE a.BoxID IN (297,
318,
337,
354,
397,
398,
504,
535,
557,
604,
733)
January 10, 2014 at 9:05 am
igloo21 (1/10/2014)
Hello-I’m having a minor issue and I’m having trouble figuring out why. So basically all I want to do is update one column using the UPDATE and SELECT below. I’m expecting to update roughly 17,600 records which is what I get back when I run just the SELECT without the UPDATE. When I run the script with the UPDATE included I end up updating over 55,000 rows (in my test environment of course ?)
I think it may be something I’m doing wrong in the UPDATE part. Can someone please give me a hint what I’m doing wrong?
UPDATE ProductssSET HotLeadParticipant=0 WHERE EXISTS(
SELECT DISTINCT
a.BoxID
,a.BoxName
,p.ProductsID
,p.ProductsName
,p.HotLeadParticipant
FROM
dbo.ValidProducts vp
INNER JOIN dbo.BoxProducts ap
ON vp.BoxProductID = ap.BoxProductID
INNER JOIN dbo.Boxs a
ON ap.BoxID = a.BoxID
INNER JOIN dbo.tblProducts p
ON vp.ProductID = p.ProductsD
WHERE a.BoxID IN (297,
318,
337,
354,
397,
398,
504,
535,
557,
604,
733)
Also remember that it is only going to update the number of products which might be less then the number of rows returned in the select statement because there might be more than one row per product.
January 10, 2014 at 10:22 am
davidandrews13 (1/10/2014)
i'm presuming that the code in your EXISTS statement always brings back at least one row?it looks like, because it always brings back data, the EXISTS statement will always come back TRUE.
so your basically saying:
UPDATE Productss
SET HotLeadParticipant=0
WHERE
1 = 1 --this will always be true
that means your updating every record in your table..
Yea I believe your correct now that I think about it. :blush:
January 10, 2014 at 10:27 am
Keith Tate (1/10/2014)
Actually no it's not spelled with two s's I did some code scrubbing before posting it and I missed that:-D.
But yea, I suppose can join Products.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply