Update Statement with an inner join in DB2

Question

Post reply

Update Statement with an inner join in DB2

BH-428850

Ten Centuries

Points: 1038
More actions
August 15, 2007 at 7:32 am

#118122

I have data that I've imported into a table in the AS/400 (DB2) from SQL 2000. I need to update a column in a table that previously existed on the AS/400 with the new data I've imported from SQL 2000.
Any idea how to do an update that contains an inner join in DB2?> >
>Here is the problem:>
>Table1.Column2 (FFIITMAP. FFIHGTH) all records are blank
and I need to update them with the values from Table2.Column2 (cubiscan.CFFIHGTH)
where Table1.Column1(FFIITMAP.FFIITMN) = Table2.Column1(cubiscan.CFFIITMN)>
>This is how I would do it within SQL 2000
--DB2 Doesn't like the From>
>Update idsdemo.FFIITMAP
set FFIHGTH = CFFIHGTH
From idsdemo.FFIITMAP
inner join idsdemo.cubiscan on ltrim(rtrim(idsdemo.cubiscan.CFFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN))>
>And these two are ways the internet suggested but still won’t work: >
>--DB2 Says invalid Tokens
Update idsdemo.FFIITMAP
inner join idsdemo.cubiscan on ltrim(rtrim(idsdemo.cubiscan.CFFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN))
set FFIHGTH = CFFIHGTH >
>--DB2 Says it returns more than one row
Update idsdemo.FFIITMAP
set FFIHGTH =
(select CFFIHGTH from idsdemo.cubiscan
inner join idsdemo.FFIITMAP on ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN)) = ltrim(rtrim(idsdemo.cubiscan.CFFIITMN))
)>
>>>>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no need to cross post the same question in different forums. the Active Topics shows all new stuff.

continue and answered here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=390752

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BH-428850 Ten Centuries Points: 1038 More actions · Answer 2

I had noticed I posted the original question under a category that was not consistent with my question so I posted under a category that made more sense, however I didn't know how to remove the original posting. Sorry...

David-250683 Hall of Fame Points: 3452 More actions · Answer 3

That's ok BH. This place is filled with anal people.