Q:
SQL/400 Query to update one field in File1 with other field in File2
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.
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.
ASKED:
Aug 6 2009 12:06 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
"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
