Problems with an Update Statement containing an inner join within DB2

Question

Post reply

Problems with an Update Statement containing an inner join within DB2

BH-428850

Ten Centuries

Points: 1038
More actions
August 14, 2007 at 4:08 pm

#69673

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 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the table idsdemo.FFIITMAP is already part of the query, so you don't want to say FROM <itself>

you want to say FROM <other table> and then add the join conditions.

I think this is what you are looking for:

Update idsdemo.FFIITMAP

set idsdemo.FFIITMAP.FFIHGTH = idsdemo.cubiscan.CFFIHGTH

From idsdemo.cubiscan

WHERE ltrim(rtrim(idsdemo.cubiscan.CFFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN))

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 gave that a try but it still gives me an error dealing with the FROM causing a problem.

Keyword FROM not expected. Valid tokens: <END-OF-STATEMENT>.

Lowell SSC Guru Points: 323518 More actions · Answer 3

wierd...it's ok syntax wise, so I suspect is has to do with teh multi-part-name.

I'd suggest aliasing the table to fix it:

Update idsdemo.FFIITMAP

set idsdemo.FFIITMAP.FFIHGTH = A.CFFIHGTH

From idsdemo.cubiscan A

WHERE ltrim(rtrim(A.CFFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN))

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 4

I tried that as well but I got another error: Column qualifier or table A undefined.

I actually run without the table name prefix in the set and it still gives me an error on the FROM. Is the syntax that much different in DB2 than SQL2000? I'm used to working with SQL2000 but not so much with DB2.

Update idsdemo.FFIITMAP

set FFIHGTH = CFFIHGTH

From idsdemo.cubiscan

WHERE ltrim(rtrim(idsdemo.cubiscan.CFFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN))

Lowell SSC Guru Points: 323518 More actions · Answer 5

since this is db2 instead of sql, does that mean the idsdemo.FFIITMAP is not databasename.tablename? so the tablename has a period in it? ie [idsdemo.FFIITMAP]

if that's true, then we'd just add [] brackets to allow the name to be evaluated:

Update [idsdemo.FFIITMAP]

set FFIHGTH = CFFIHGTH

From [idsdemo.cubiscan]

WHERE ltrim(rtrim([idsdemo.cubiscan].CFFIITMN)) = ltrim(rtrim([idsdemo.FFIITMAP].FFIITMN))

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 6

No, the DB name is idsdemo and table is FFIITMAP the same as SQL is. I tried the suggestion anyways and it produced an error on the [

I think it is just unfamiliar with a FROM within an Update Statement for some reason. I tried to work around the problem and not use the FROM by using a inner select but it says it returns more than 1 record (which I'm aware of and trying to do) however it doesn't give me a syntax error. Either way I can't get it to update the data.

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))

)

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

Okay in an attempt to accomplish the same goal I've written a cursor however DB2 seems to have a problem with my variables that are declared. Anyone familiar with DB2 syntax or how I need to write a cursor to get it to execute?

declare @CFFIITMN as varchar (10)

declare @CFFIHGTH as varchar (10)

declare Cubiscan_Cursor cursor for

select ltrim(rtrim(CFFIITMN)), CFFIHGTH

FROM idsdemo.cubiscan

open Cubiscan_Cursor

fetch next from Cubiscan_Cursor into @CFFIITMN, @CFFIHGTH

while @@fetch_status = 0

begin

Update idsdemo.FFIITMAP

Set FFIHGTH = @CFFIHGTH

WHERE ltrim(rtrim(FFIITMN)) = @CFFIITMN

fetch next from Cubiscan_Cursor into @CFFIITMN, @CFFIHGTH

end

close Cubiscan_Cursor

deallocate Cubiscan_Cursor

Ken McKelvey SSCoach Points: 18843 More actions · Answer 8

I think the following is standard ANSI so it may work:

UPDATE FFIITMAP

SET FFIHGTH = (

SELECT MAX(C.CFFIHGTH)

FROM cubiscan C

WHERE C.CFFIITMN = FFIITMAP.FFIITMN

GROUP BY C.CFFIITMN

)

WHERE EXISTS (

SELECT *

FROM cubiscan C1

WHERE C1.CFFIITMN = FFIITMAP.FFIITMN

)

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

I forgot to post what I used that finally allowed me to update the field. The script is below.

UPDATE idsdemo.FFIITMAP

SET FFIHGTH = (SELECT FFIHGTH

FROM idsdemo.cubiscan1

WHERE ltrim(rtrim(idsdemo.cubiscan1.FFIITMN)) = ltrim(rtrim(idsdemo.FFIITMAP.FFIITMN)))

Where ltrim(rtrim(FFIITMN)) in (select ltrim(rtrim(FFIITMN)) from idsdemo.cubiscan1)