Update database using a query for DB2

[heatmiserkmp]

I need to "match up" data from 2 files and then change a field in one of hte files. Here is my statement:

update OSFXCPP
set fxxest = 'O'
where (SELECT J0D3DT, FXAXCD, FXA7TX, FXXEST
FROM OSJ0CPP, OSFXCPP
WHERE J0D3DT = 1050427
AND J0AXCD = fxaxcd
AND j0a7tx = fxa7tx
AND J0AXCD= '409'
AND FXXEST = 'C')

I dont do this very often, I am hopeing someone can give me a quick tip on what is wrong here. If I run:

SELECT J0D3DT, FXAXCD, FXA7TX, FXXEST
FROM OSJ0CPP, OSFXCPP
WHERE J0D3DT = 1050427
AND J0AXCD = fxaxcd
AND j0a7tx = fxa7tx
AND J0AXCD= '409'
AND FXXEST = 'C'

I get the list of data, but I want to update the field that is C to an O.

Thanks for any help here. the db2 error i am getting is that the J0D3DT token is not valid.

 

[MarcLodge]

Hi Heat,
Your SQL consists of an inner query (the SELECT that works perfectly on it's own) and an outer query (the update statement). Your problem is that you have no link between the two queries.

What you need to do is something like:

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

Hope this helps

Marc

You may have to move the FXXEST =

 

[heatmiserkmp]

Dont I need to put both table names in the outer select statement?

 

[MarcLodge]

The outer update statement only needs to have the name of the table you are updating in it.

What happens if you run the SQL supplied above?

 

[das6732]

You need to enclose that value in quotes