Re: SQL update question

From: Michael Ringbo <mri_at_dde-nospam.dk>
Date: Wed, 10 Mar 1999 10:38:18 +0100
Message-ID: <36E63D8A.D51171D5@dde-nospam.dk>

Hi,

What's wrong is that the join of the two fid-columns should be inside the nestet query. But be aware: If not all records in the sw_filemgm table match a record in the sw_upload table, you will end up having null's in some longitude columns (namely the ones of the records that don't match a record in sw_upload. To avoid this, your update statement needs two nestet joins. So your update should look like this:

update sw_filemgm
set sw_filemgm.longitude = (select sw_upload.longitude

                                          from sw_upload
                                         where sw_filemgm.fid =

sw_upload.fid)
where exists (select null

                     from sw_upload
                     where sw_filemgm.fid = sw_upload.fid)

;

Hope this helps!

Regards,

Michael Ringbo

Mike Ellenbecker wrote:

> what wrong with this SQL statement?
>
> table to update sw_filemgm
> table updating from sw_upload
> unquie id to both tables FID
>
> 1 update sw_filemgm
> 2 set sw_filemgm.longitude = (select sw_upload.longitude
> 3 from sw_upload)
> 4 where sw_filemgm.fid = sw_upload.fid;
>
> ERROR at line 4:
> ORA-00904: invalid column name
>
> the column names are correct.
>
> Thanks in Advance
>
> Mike Ellenbecker
Received on Wed Mar 10 1999 - 03:38:18 CST