Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: UPDATE QUERY ADO error ORA-00904
ora-00904 means invalid column name. Can you post the results of desc
currentprices from sqlplus, as your table create statement is a sqlserver
creation statement not an oracle one.
-- Niall Litchfield Oracle DBA Audit Commission UK "AkeSoft" <ake.bendel_at_vsmarket.com> wrote in message news:be5c6f9.0304080138.2c2bcefe_at_posting.google.com...Received on Tue Apr 08 2003 - 05:01:48 CDT
> Hi
>
> I have a problem to update one table from another in ORACLE 8i on
> UNIX.
>
> I hav eone table where i put in the latest information
> (CURRENTPRICES_TEMP) then i only want to update (CURRENTPRICES) with
> information that hav been changed since I last did the update.
>
> Here's the query I have tried now ( I have tried alot I'm more sql
> 2000 user so I have tried JOIN and other non working stuff before) As
> far I have read this is the way to do it? but I only get ADO error
> ORA-00904 Invalid column name, I have tried this direct on the Oracle
> server and from MS SQL server 2000 with a odbc connection. Please help
> me soon I going crazy on this!.
>
> UPDATE CURRENTPRICES CU
> SET (QUOTEHISTORYDATE,
> LATEST,
> BUY,
> SELL,
> HIGHEST,
> LOWEST,
> CLOSEPRICE,
> NETCHANGE,
> PERCENTCHANGE,
> NETTURNOVER,
> TURNOVER) = (SELECT
> CUT.QUOTEHISTORYDATE,
> CUT.LATEST,
> CUT.BUY,
> CUT.SELL,
> CUT.HIGHEST,
> CUT.LOWEST,
> CUT.CLOSEPRICE,
> CUT.NETCHANGE,
> CUT.PERCENTCHANGE,
> CUT.NETTURNOVER,
> CUT.TURNOVER
> FROM CURRENTPRICES_TEMP CUT
> WHERE CU.ISINCODE = CUT.ISINCODE AND
> CU.TURNOVER < CUT.TURNOVER)
> WHERE EXISTS (SELECT 1 AS ONE
> FROM CURRENTPRICES CU,
> CURRENTPRICES_TEMP CUT
> WHERE CU.ISINCODE = CUT.ISINCODE AND
> CU.TURNOVER < CUT.TURNOVER) AND
> (ISINCODE = CURRENTPRICES_TEMP.ISINCODE) AND
> (TURNOVER < CURRENTPRICES_TEMP.TURNOVER)
>
> Here's how the tables looks like, both are identical.
>
> CREATE TABLE [dbo].[CURRENTPRICES] (
> [ISINCODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [QUOTEHISTORYDATE] [datetime] NOT NULL ,
> [LATEST] [float] NOT NULL ,
> [BUY] [float] NOT NULL ,
> [SELL] [float] NOT NULL ,
> [HIGHEST] [float] NOT NULL ,
> [LOWEST] [float] NOT NULL ,
> [CLOSEPRICE] [float] NOT NULL ,
> [NETCHANGE] [float] NOT NULL ,
> [PERCENTCHANGE] [float] NOT NULL ,
> [NETTURNOVER] [float] NOT NULL ,
> [TURNOVER] [float] NOT NULL
>
>
> Please help me or I get fired.