How to use the SQL update statement with a join to another file to get the correct selection

30 pts.
Tags:
SQL
SQL/400
select count(op140m.pfpn) from prdat/op140m join cgoderi/op140mup ON op140m.PFCORP = op140mup.PFCORP and op140m.PFSTCN = op140mup.PFSTCN and op140m.PFPN = op140mup.PFPN and op140m.PFSTCN = p140mup.PFSTCN and op140m.PFEFDT = op140mup.PFEFDT

I have 1 table op140mup with 8088 records and op140m with over 400000 records. I want to update 1 field in op140m but only for all based on the join with op140mup (5 join fields). The count in the above statement is correct but what would be the syntax if I want to use update in stead of select? Can I use update in combination with a join?

Any help is appreciated.

 



Software/Hardware used:
I-Series SQL/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi, Chrisjegoderie

This may be along the lines you’re looking for. Instead of a join, perhaps subselects like this will work for you:
<pre>
Update prdat/op140m A
Set pfpn = <some value?>
Where Exists
(Select B.PFCORP
From cgoderi/op140mup B
Where B.PFCORP = A.PFCORP
and B.PFSTCN = A.PFSTCN
and B.PFPN = A.PFPN
and B.PFSTCN = A.PFSTCN
and B.PFEFDT = A.PFEFDT)<pre></pre></pre>

Discuss This Question: 1  Reply

 
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
  • Chrisjegoderie
    Hi Craig, thanks very much, the syntax is perfect and I could update the field. Chris
    30 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