SQL query not working 2

[Bluejay07]

Hello,

I have a query that updates certain fields based on a select query. The problem is that the update seems to be working on every single row in the database regardless of the select statement. It does not appear that the select statement is being executed.

UPDATE OEORDD
SET 
   QTYORDERED = 0, 
   QTYBACKORD = 0,
               
   TBASE1 = 0,
   TBASE2 = 0,
   TBASE3 = 0,
   TBASE4 = 0,
   TBASE5 = 0,
               
   TAMOUNT1 = 0,
   TAMOUNT2 = 0,
   TAMOUNT3 = 0,
   TAMOUNT4 = 0,
   TAMOUNT5 = 0,
               
   TRATE1 = 0,
   TRATE2 = 0,
   TRATE3 = 0,
   TRATE4 = 0,
   TRATE5 = 0,
               
   COMPLETE = 2
WHERE EXISTS
   (SELECT OEORDD.*
    FROM OEORDH INNER JOIN
         OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
    WHERE OEORDH.COMPLETE < 3 AND OEORDD.QTYBACKORD > 0 AND OEORDD.QTYSHPTODT > 0)

A second update query follows this code but the structure is the same. If I can get this working, I will modify the associated query.

Could anyone help me to determine why my query is not working on a few rows and instead it changes every single row. Any help is greatly appreciated.

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[simian336]

Your subquery produces the desired results?

Simi

 

[Bluejay07]

Yes, the select query seems to work correctly.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

Does this work?

UPDATE OEORDD
SET    QTYORDERED = 0, 
       QTYBACKORD = 0,
               
       TBASE1 = 0,
       TBASE2 = 0,
       TBASE3 = 0,
       TBASE4 = 0,
       TBASE5 = 0,
               
       TAMOUNT1 = 0,
       TAMOUNT2 = 0,
       TAMOUNT3 = 0,
       TAMOUNT4 = 0,
       TAMOUNT5 = 0,
               
       TRATE1 = 0,
       TRATE2 = 0,
       TRATE3 = 0,
       TRATE4 = 0,
       TRATE5 = 0,
               
       COMPLETE = 2
FROM   OEORDH INNER JOIN
       OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
WHERE  OEORDH.COMPLETE < 3 
       AND OEORDD.QTYBACKORD > 0 
       AND OEORDD.QTYSHPTODT > 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

What if you just run...

UPDATE OEORDD
SET
D.QTYORDERED = 0,
D.QTYBACKORD = 0,
                  
D.TBASE1 = 0,   
D.TBASE2 = 0,   
D.TBASE3 = 0,   
D.TBASE4 = 0,   
D.TBASE5 = 0,                  

D.TAMOUNT1 = 0,   
D.TAMOUNT2 = 0,   
D.TAMOUNT3 = 0,   
D.TAMOUNT4 = 0,   
D.TAMOUNT5 = 0,                  

D.TRATE1 = 0,   
D.TRATE2 = 0,   
D.TRATE3 = 0,   
D.TRATE4 = 0,   
D.TRATE5 = 0,                  

D.COMPLETE = 2

FROM OEORDH H
INNER JOIN OEORDD D
	ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ    
WHERE OEORDH.COMPLETE < 3 
	AND OEORDD.QTYBACKORD > 0 
	AND OEORDD.QTYSHPTODT > 0

Simi

 

[simian336]

You beat me George...

Simi

 

[Bluejay07]

Great minds think alike.
That code arrangement seems to work.

Thank you so much to both of you.

If at first you don't succeed, then sky diving wasn't meant for you!