Re: SQL update statement problem!

Hi Adrian,
The correct statement is:
Update nls_crafts
SET inc_in_renew = 1
where exists
(select 'x'
 from nls_tariffs
 where nls_crafts.tariff_id = nls_tariffs.tariff_id AND  nls_tariffs.inc_in_renew = 1);

You can't join in updates only subqueries are possible.

Hth,

Sybrand Bakker, Oracle DBA

Adrian Harrison <adrianh_at_globalnet.co.uk> wrote in message news:37c0d545.2729314_at_news.globalnet.co.uk...
> Using Oracle 7.3.4
>
> I'm trying to create a SQL statement that will update (see tables below),
nls_crafts.inc_in_renew
> field to 1 if nls_tariffs.inc_in_renew field = 1. The fields are related
via the tariff_id field.
>
> nls_crafts nls_tariffs
> =========== ===========
> tariff_id (Foreign Key) tariff_id (Primary Key)
> inc_in_renew inc_in_renew
>
> Something like -
>
> UPDATE nls_crafts SET inc_in_renew = 1 WHERE nls_crafts.tariff_id =
nls_tariffs.tariff_id AND
> nls_tariffs.inc_in_renew = 1;
>
> I keep getting error "ORA-00904" - invalid column name on
"nls_tariffs.inc_in_renew" field even
> though the column definately exists
>
> Any ideas? - I'm sure it must be something really simple!
>
> Thanks
>
> Adrian Harrison
Received on Sun Aug 22 1999 - 06:22:03 CDT