SQL code made simpler

PSMurray

530 pts.

Tags:

CRTDUPOBJ

iseries v5r4

SQL

SQL Server

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

Software/Hardware used:
V5R4M0

Asked: June 23, 2011 7:11 PM Last updated: June 24, 2011 12:55 PM

Answer Wiki

Discuss This Question: 4 Replies

carlosdl Jun 23, 2011 8:43 PM GMT
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.
84,495 pointsBadges:
report
TomLiotta Jun 24, 2011 1:33 AM GMT

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

125,585 pointsBadges:

report
The Most-Watched IT Questions: June 28, 2011 - ITKE Community Blog Jun 28, 2011 7:00 AM GMT

[...] 5. Carlosdl and TomLiotta try to help making some SQL code simpler. [...]

0 pointsBadges:

report
The Most-Watched IT Questions: July 5, 2011 - ITKE Community Blog Jul 5, 2011 2:59 PM GMT

[...] 3. Carlosdl and TomLiotta help out a fellow member: SQL code made simpler. [...]

0 pointsBadges:

report