SQL UPDATE Statement on AS400

[DownwithFescue]

Can someone help me out with this statement?
update testdta/f0101 a set a.abat1 = 'XV'
where (select * from testdta/f0101 as c left outer join
testdta/f0401 as b
on c.aban8 = b.a6an8 where c.abat1 = 'V' And b.a6AYPD = ' ' And
b.a6APPD = ' ')

 

[klwong]

Within the parentheses, you should not do "select *". Pick the field that you want to use for the where clause of your update statement. Like:

where a.aban8 in (select c.aban8 from ...

 

[DownwithFescue]

Still not working
Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !<
update testdta/f0101 a set a.abat1 = 'XV'
where (select a6an8 from testdta/f0101 as c left outer join
testdta/f0401 as b
on c.aban8 = b.a6an8 where c.abat1 = 'V' And b.a6AYPD = ' ' And
b.a6APPD = ' ')

 

[pfd]

You should ask this query in the World forum. That is where all the AS400 SQL gurus are!! Try this:

update testdta/f0101 a set set a.abat1 = 'XV' where
exists (select * from testdta/f0401 b where b.a6an8=a.aban8
and a.abat1 = 'V' And b.a6AYPD = ' ' and b.a6APPD = ' ')

I realize mine is an inner join where as your join was a left outer join but depending on the data that may not be a problem.

Thanks,
Matt

 

[DownwithFescue]

Got it thanks

 

[ice_cube210]

Try this

update testdta/f0101 set abat1 = 'XV'
where aban8 in (select a6an8 from testdta/f0401 b,
testdta/f0101 c
where c.aban8 = b.a6an8 and c.abat1 = 'V' And b.a6AYPD = 0 And
b.a6APPD = 0)


You were still missing the aban8 in as suggested by Ken , and also AYPD and APPD are numeric amount fields. So you cannot check for ' '. Instead check for zero . Of course run the select statement first to verify it returns the data you are expecting