SQL/400 Query to update one field in File1 with other field in File2

695 pts.
Tags:
AS/400
AS/400 queries
SQL/400
Hi All, I want to update one field(PCUSR6) in NBSPCNTR file for all records with MCUSR6 field from CASCNTRM file. I tried below combinations, but i couldn't do it. Can any one please help to update whole file with another file field. Please find below queries i tried: i have to update NBSPCNTR file field PCUSR6 with CASCNTRM file field MCUSR6 on matching of PCPOLN from NBSPCNTR and MCCNTR from CASCNTRM 1. UPDATE NBSPCNTR Set PCUSR6 = (SELECT MCUSR6 FROM CASCNTRM where NBSpcntr.PCPOLN = cascntrm.MCCNTR) WHERE nbspcntr.PCPOLN IN (Select MCCNTR FROM CASCNTRM) Error i got is: Result of SELECT more than one row. 2.UPDATE NBSPCNTR a SET PCUSR6 = (SELECT b.MCUSR6 FROM CASCNTRM b WHERE a.Pcpoln = b.mccntr) WHERE EXISTS (SELECT b.mcusr6 FROM cascntrm b WHERE a.Pcpoln = b.mccntr) Error:Result of SELECT more than one row. 3.UPDATE NBSPCNTR A Set PCUSR6 = coalesce((selECT MCUSR6 FROM cascntrm B where A.PCPOLN = B.MCCNTR), PCUSR6) Error: Result of SELECT more than one row. 4.UPDATE NBSPCNTR A Set PCUSR6 = (SELECT MCUSR6 FROM cascntrm B where A.PCPOLN = B.MCCNTR) Error: Null values not allowed in column or variable PCUSR6. Thanks in advance.

Answer Wiki

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

#2 would work except that the clause
“SELECT b.MCUSR6
FROM CASCNTRM b
WHERE a.Pcpoln = b.mccntr”

change the inner select to distinct
try “SELECT distinct b.MCUSR6
FROM CASCNTRM b
WHERE a.Pcpoln = b.mccntr”

this would work if the mulitple values returned are the same

Find the problem

Select count(*), PCPOLN, MCCNTR
from (select distinct PCPOLN from NBSPCNTR)
join CaSCNTRM on PCPOLN = MCCNTR
group by PCPOLN, MCCNTR
having count(*) > 1

You will have to check out the values of MCUSR6 in any > 1 to see if they match – these would be solved with the distinct. But you will have to determne what to do with the matches that bring different values from MCUSR6

Phil

Discuss This Question:  

 
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

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