UPDATE query

[GrahamR]

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.

 

[Ofer]

If you have only one record in each table, and you don't need to link the two
tables, then try this

Update TableA, TableB Set TableA.FieldA = TableB.FieldA ,TableA.FieldB
=TableB.FieldB

 

[GrahamR]

Thanks, works fine!

If you have only one record in each table, and you don't need to link the two
tables, then try this

Update TableA, TableB Set TableA.FieldA = TableB.FieldA ,TableA.FieldB
=TableB.FieldB
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[GrahamR]

Thankyou for the prompt reply. This helps a lot.

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.

 

[Chris2]

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.