Trying to update field from another table using SQL - SOLVED

[flyingfox777]

Hi,
Simple problem but not working.
Working with Base as the frontend.

I have two tables (A&B) in the same database which are linked by field "CTP_ID".
CTP_ID in table A is the primary key, in table B it is an integer.
Table B has a separate field, ID, as the primary key.
Both tables contain a field C.
(For info Table B is an older version of Table A but with the addition of the ID field and CT_ID changed to an integer).

I wish to write an SQL expression that updates field C values in table A with those that are in table B. (I can display the two fields using a simple query from the query builder.)


Query used:-
UPDATE "A" set "A"."C"="B"."C" WHERE EXISTS (SELECT 1 FROM "A" WHERE "A"."CTP_ID" = "B"."CTP_ID" )

All attempts to-date fail :- user lacks privilege or object not found: B.CTP_ID.

Any pointers would be very much appreciated.
_________________
Always in the proverbial - only the depth varies.
Running LibreOffice 3.5.4.2, HSQLDB 2.2.9 with JDBC (separate database from frontend) on Ubuntu 12.04 X86-64 all on one machine.

[Sliderule]

You should be able to use the following SQL ( Structured Query Language ) to make the modifications you described.

It is important to note, that, this SQL Code can only be executed, EITHER:

1. From the Menu:
   
   Tools -> SQL...
   
   
2. From within a Macro

[flyingfox777]

Many thanks Sliderule.javascript:emoticon('')
Solution worked a treat.
_________________
Always in the proverbial - only the depth varies.
Running LibreOffice 3.5.4.2, HSQLDB 2.2.9 with JDBC (separate database from frontend) on Ubuntu 12.04 X86-64 all on one machine.