I have 2 files that are clones - created by DUPOBJ. File_A is the original. File_BU is the copy. After I finish processing File_A, I want to update the same column (tSTAdm) in File_A when it is not equal to the same column (tStAdm) in File_BU. The file has a unique key – TsStat.
I got the following code to work, but there must be a simpler code.
UPDATE File_a as T01 SET T01.tStADM = (Select T03.tStAdm from File_BU as T03 where T01.TsStat = T03.TsStat) Where T01.TsStat in (Select T02.TsStat from File_BU as T02 where T01.tStAdm <> T02.tStAdm) Phil
This is not simpler, but it could be faster in some circumstances:
UPDATE File_a as T01 SET T01.tStADM =
(Select tStAdm from File_BU WHERE T01.TsStat = TsStat)
WHERE EXISTS (SELECT 1
FROM File_BU
WHERE TsStat = T01.TsStat
AND tStADM <> T01.tStADM)
I can't think of a simpler way to do it. SQL server allows the use of JOINs in UPDATEs, and I think DB2 on some platforms has a MERGE statement that could probably simplify this task, but those commands are not part of the SQL standard.
I want to update the same column (tSTAdm) in File_A when it is not equal to the same column (tStAdm) in File_BU.
Because of "a unique key – TsStat", I assume you mean that you simply want File_a and File_BU to have matching tStADM values, based on matches for TsStat.
But you don't want to UPDATE all rows because many rows already have correct (i.e., matching) values.
I'd say Carlosdl has the right answer. And if performance is the main reason to avoid just doing a straight UPDATE, then it's probably the best answer.
Tom
Discuss This Question: 4  Replies