Update Joined tables

[vagasv1]

Hi I would like to update a table with the info from another table ie.

table one.
style status
------- ----------
1 design
2 design
3 buy
4 buy

table two
style colour status
-------- -------- --------
1 blue
1 black
1 green
2 blue
3 red
3 green

what I want is to be able to update table 2 with the statuses from table 1 depending that the style number is equal

UPDATE one A JOIN two B ON 
A.style= B.style
SET A.status= B.status

but this does not seem to work.

I am running this on a AS400/iSeries

thanks for your help in advance

 

[AlexCuse]

something like this?

update two
set two.status = one.status
from one inner join two
on one.style = two.style

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[vagasv1]

thanks for your help Alex but this is not working either the error message is - Column qualifier or table ONE is undefined. then the cursor is flashing on the one.staus

 

[AlexCuse]

Are those your real table names (one and two?)?

I am not really familiar with the AS/400, but I don't know why that would not work if correct table names were in there.

If one and two are correct table names, then I guess you could try something like this?

update two
set two.status = one.status
from one, two
where two.style = one.style

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[LKBrwnDBA]

-- OR --

update two
set status = (
Select status
  from one 
 where one.style = two.style )

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb