Re: UPDATE query


"GrahamR" <GrahamR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DAF815F1-6B71-4F1B-8B0C-0F5180F0887B@xxxxxxxxxxxxxxxx
> I am struggling to get the syntax right for an UPDATE query with a
SUBSELECT
> in MS Access 2003. I am not sure if the SQL is possible either.
>
> I have 2 tables which I am using to temporarily store some values. I
want to
> update 2 fields of one table with 2 corresponding fields of another
table.
>
> Both tables only have a single row and are not related to any other
tables (
> purely used as a temporary storage area ). The table record that is
to
> receive the update already has one column with a value in it , which
I do not
> want to be overwritten, hence wanting to do an UPDATE query.
>
> I thought the basic syntax should be along the lines of
>
> UPDATE TableA SET (fieldA, FieldB)
> FROM ( SELECT FieldA, FieldB from TableB).
>
> I have tried various ways of setting the syntax, but just get the
usual
> 'Syntax Error' message which doesn't highlight which particular part
of the
> syntax is wrong.
>
> I don't seem to be able to find any examples of such syntax in the
Help
> files or in the text books I own.
>

GrahamR,

You are receiving a syntax error because the UPDATE statement has no
FROM clause.

The syntax is:

UPDATE <table>
SET <column>
WHERE <criteria>

What you need to do is:

(Please forgive the dates appended to the table names.)

CREATE TABLE TableA_10232005_1
(TableAID AUTOINCREMENT
,fieldA INTEGER
,fieldB INTEGER
,CONSTRAINT pk_TableA_10232005_1 PRIMARY KEY (TableAID)
)

CREATE TABLE TableB_10232005_1
(TableBID AUTOINCREMENT
,fieldA INTEGER
,fieldB INTEGER
,CONSTRAINT pk_TableB_10232005_1 PRIMARY KEY (TableBID)
)

Sample Data

TableA
1, 5, 10

TableB
1, 100, 200

UPDATE TableA_10232005_1 AS TA1
INNER JOIN
TableB_10232005_1 AS TB1
ON TA1.TableAID = TB1.TableBID
SET TA1.fieldA = TB1.fieldA
,TA1.fieldB = TB1.fieldB

Result:

TableA
1, 100, 200


Sincerely,

Chris O.


.

Relevant Pages

  • Which inner join syntax is more efficent?
    ... Assuming both TableA and TableB are roughly the same size with index ... Which syntax will be more efficient? ... TableA AS A INNER JOIN TableB AS B ON A.Col_1 = B.Col_1 ...
    (microsoft.public.sqlserver.server)
  • Re: Querry giving incorrect result
    ... you can do this with the help of INSERT INTO...SELECT syntax. ... insert into baddata ... from tablea ... (select * from tableb ...
    (microsoft.public.sqlserver.mseq)
  • Re: Query problem
    ... >cant figure out the syntax of the query. ... SELECT tableA.* ... FROM tableA LEFT JOIN tableB ...
    (microsoft.public.access.formscoding)
  • Re: Which inner join syntax is more efficent?
    ... I would look at the execution plan generated by both. ... > Assuming both TableA and TableB are roughly the same size with index ... Which syntax will be more efficient? ...
    (microsoft.public.sqlserver.server)
  • Re: Update TableA with data from TableB using Update Query
    ... I was hoping to do this with an update query as I don't SQL...Don't mind ... UPDATE prices INNER JOIN newPrices ... FROM tableA INNER JOIN tableB ...
    (microsoft.public.access.queries)