SQL question

[pfd]

A user ran R31410 wide open and updated all our Work orders to the wrong status. Now I am trying to update it back to the correct status.
But my SQL below is blanking out the WO status.
Here is my SQL:
update py/f4801 a set a.wasrst =(select b.prurcd from
py/f55pr b where a.wadoco=b.prurab and
a.wamcu=b.prmcu and a.wamcu=' 123100') where exists
(select * from py/f55pr b where a.wadoco=b.prurab and
a.wamcu=b.prmcu and a.wamcu=' 123100')
When I do the Select it does come back with the right # of records. But I cant figure out why it is blanking out the status. The URCD field does have the right status. Any ideas would be much appreciated.

Thanks,
Matt

 

[JMast]

Maybe it is too late in the day, or maybe SQL is different in DB2, but isn't the 'where exists....' statement redundant? It looks to me like all you need is the simple update you have at the beginning.

The other suggestion, especially in times of pressure to fix problems, is to do things in multiple simple steps using temp tables so you can check your results and validate/fix more easily.

Jer

 

[pfd]

Thanks for the suggestion. My temp table is what I am joining to F4801. If I did not use the exists clause it would try to update every work order in the system. I dont want that to happen since we have multiple business units in the system and I would have created a bigger problem. At least for this issue I can blame the user!

Thanks,
Matt

 

[JMast]

Ok, I guess I just don't understand. It looks to me like your where clauses are exactly the same (where a.wadoco=b.prurab and a.wamcu=b.prmcu and a.wamcu=' 123100') so the second query is going to pull back exactly the same records as the first query.

From the post, it looks like your temp table was loaded with the docos and correct statuses which, combined with your mcu criteria should prevent updating all the work orders in the system. So, I was just suggesting you pull the queries apart by doing the select * first before the update instead of trying to figure out the issue with the compound query when you are under pressure.

It seems I don't understand the syntax of DB2 or your situation well enough. Sorry I didn't help, thought it was worth a shot.

Jer

 

[G_Smith]

Jer,

The first sub-select is to retrieve the matching value for the field (column) to be updated. The where exists or second sub-select is to insure the update is to the correct record (row). The column select must return a unique row for the update as only one value can be put in the updated field (column).

Matt,

Also many times the where exists doesn't always seem to be tight enough. You might try to re-write the update statement as a join/select just to show the fields from both records that are going to be used in the update. It might give you a clue as to the problem.

Also since the where exists is a question, you might try - " Where a.wamcu = ' 123100' and (a.wadoco, a.wamcu) in (select b.prurab, b.prmcu from py/f55pr b) ".

 

[Ericl]

The Row value expression may be more understandable and intuitive than the Where Exists expression:
See http://www.itjungle.com/fhg/fhg041410-story02.html