SQL code made simpler

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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    69,510 pointsBadges:
    report
  • TomLiotta
    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
  • PSMurray
    [...] 5. Carlosdl and TomLiotta try to help making some SQL code simpler. [...]
    0 pointsBadges:
    report
  • PSMurray
    [...] 3. Carlosdl and TomLiotta help out a fellow member: SQL code made simpler. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following