Update with Select...

Question

Post reply

Update with Select...

aury gp

SSC Rookie

Points: 40
More actions
February 7, 2007 at 9:08 am

#153421

Hi,
I don't know what is wrong
I have this T-SQL
Select 1:
SELECT PRODDTA.F0911.[GLREG#]
FROM PRODDTA.F0911
WHERE (((PRODDTA.F0911.GLLT)='AA') AND ((PRODDTA.F0911.GLPN)=12)
AND ((PRODDTA.F0911.GLCTRY)=20) AND ((PRODDTA.F0911.GLFY)=5))
And the result are 200.000 of rows
Then I use it to update with this T-SQL
Select 2:
UPDATE PRODDTA.F0911
SET PRODDTA.F0911.[GLREG#] = 0
WHERE PRODDTA.F0911.[GLREG#] IN
( SELECT PRODDTA.F0911.[GLREG#]
FROM PRODDTA.F0911
WHERE (((PRODDTA.F0911.GLLT)='AA') AND ((PRODDTA.F0911.GLPN)=12)
AND ((PRODDTA.F0911.GLCTRY)=20) AND ((PRODDTA.F0911.GLFY)=5))
);
And the result are 4000000 of rows
That I want to do is to updates these 200.000 of records (Select 1).
What could be wrong?
Thanks, and sorry for my poor english
aurygp

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Now do a select on SELECT * FROM PRODDTA.F0911 WHERE PRODDTA.F0911.[GLREG#] =0

and see how many records are returned

Prasad Bhogadi
www.inforaise.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

This should do it for you.

UPDATE PRODDTA.F0911

SET [GLREG#] = 0

WHERE GLLT = 'AA' AND GLPN = 12

AND GLCTRY = 20 AND GLFY = 5

The reason your update query didn't work was that it was updating every row where the value of GLREG# was in the result of the first query, even if all of those rows didn't meet the filter conditions of the original query. Hope that makes sense!

John

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Makes complete sense, how did I miss? It was academic

Prasad Bhogadi
www.inforaise.com

aury gp SSC Rookie Points: 40 More actions · Answer 4

ups!!!!

Thanks guys.

Yes, you are righ. And the solution is more simple and nice.

Thanks a lot to everybody for your time.

bye

aurygp