Need help updating a field based on join of 2 tables

[heatmiserkmp]

update OSFXCPP
set fxxest = 'O'
WHERE exists (SELECT *
FROM OSJ0CPP, osfxcpp
WHERE J0D3DT = 1050427
AND J0AXCD = fxaxcd
AND j0a7tx = fxa7tx
AND J0AXCD= '409')
and FXXEST = 'C'

I know I am doing something wrong. I have 2 tables FRT BILL AND BOL, I NEED TO UPDATE THE BOL FILE, I am matching on 2 primary keys and then the actual criteria for those 2 fields. This cannot be that difficult. Any help would be much appreciated.

Thanks

 

[PHV]

Something like this ?
update OSFXCPP
set fxxest = 'O'
WHERE exists (SELECT *
FROM OSJ0CPP
WHERE J0D3DT = 1050427
AND J0AXCD = fxaxcd
AND j0a7tx = fxa7tx
AND J0AXCD= '409')
and FXXEST = 'C'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[heatmiserkmp]

I tried that and am getting the following error:

You have entered a subquery that contains a correlation
without qualification for:
Field. . . . . . . . . . . . .: FXAXCD

I am trying this on IBM AS400 DB2

Thanks for any help!

 

[PHV]

Any chance you could post the schema of OSFXCPP and OSJ0CPP with the relationships ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[heatmiserkmp]

offxcpp:

Seq Pg K Field T Len Fd Dd From To Text
5 1 FXAXCD A 3 1 3 Division Code
10 1 FXA7TX A 6 4 9 BOL Number
15 1 FXIHST A 1 10 10 BoL Type

OSJ0CPP:

Mbr : OSJ0CPP Scan:
Seq Pg K Field T Len Fd Dd From To Text
5 1 J0AXCD A 3 1 3 Division Code
10 1 J0A7TX A 6 4 9 BOL Number
15 1 J0IHST A 1 10 10 BoL Type

By schema you mean the file layout . . .

 

[PHV]

As your last post is quite irrelevant, just a guess:
UPDATE OSFXCPP
SET fxxest = 'O'
WHERE EXISTS (SELECT *
FROM OSJ0CPP
WHERE J0D3DT = 1050427
AND J0AXCD = OSFXCPP.fxaxcd
AND j0a7tx = OSFXCPP.fxa7tx
AND J0AXCD= '409')
and FXXEST = 'C'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[heatmiserkmp]

Thank You, Thank You, Thank You!