Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Statement with an inner join in DB2 Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2007 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 7:28 PM
Points: 14, Visits: 115

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

>>>> 

Post #390873
Posted Wednesday, August 15, 2007 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 12,890, Visits: 31,850

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #390899
Posted Wednesday, August 15, 2007 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 7:28 PM
Points: 14, Visits: 115

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

Post #390903
Posted Wednesday, August 15, 2007 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 11:39 AM
Points: 160, Visits: 476
That's ok BH.  This place is filled with anal people.
Post #390919
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse