[DUG] SQL - UPDATE

[Karl Reynolds]

- Most SQL variants need double brackets when using set = subselect, ie.

	update TABLENAME set COLUMNNAME = (( SUBSELECT ))

- Did you really mean to use backquote (`) delimiters? Haven't seen that
before
- Usually the COLUMNNAME part can't be prefixed (by the tablename)

So try something like:

update COMPANY c
set region_id = ((
  select a.region_id
  from AREA a
  where a.area_id = c.area_id
))

Cheers,
Carl

> -----Original Message-----
> From: delphi-bounces at ns3.123.co.nz
> [mailto:delphi-bounces at ns3.123.co.nz]On Behalf Of John
> Sent: Wednesday, October 05, 2005 9:29 PM
> To: DUG
> Subject: [DUG] SQL - UPDATE
>
>
> I wanna update the table COMPANY from a linked field. The
> following is not
> accepted. Could anybody help me with what the command should be?
>
> Thanks a lot.
> John
>
>
> UPDATE `COMPANY`
> SET `COMPANY`.Region_ID =
> (
> SELECT `Area`.Region_ID
> FROM `AREA`
> WHERE `COMPANY`.Area_ID = `Area`.Area_ID
> )
>
>
> _______________________________________________
> Delphi mailing list
> Delphi at ns3.123.co.nz
> http://ns3.123.co.nz/mailman/listinfo/delphi
>