UPDATE whole row in Table A FROM Table B

[juvi]

Hello,

I am running into problems with the UPDATE SQL.

How can I update a specific row in table A from table B?
the to tables have the same columns so is there an easy way to update the
whole row without to define the specific columns?

thank you

 

[tedmi]

The UPDATE statement is designed to operate on columns, so you must specify
all columns to be updated. If the row in Table A has a unique key, you can
try this method: save the key; delete row from Table A, read row from Table
B, if it doesn't have the same key, replace with the one from Table A, insert
a row into Table A.

CAVEAT: The Jet DB engine does not implement transactions, so if execution
is interrupted after the deletion and before insert, you will lose data. This
mehtod is OK if Table A is on a DB engine that implements transactions (e.g.
SQL Server, Oracle).

 

[John W. Vinson]

Hello,

I am running into problems with the UPDATE SQL.

How can I update a specific row in table A from table B?
the to tables have the same columns so is there an easy way to update the
whole row without to define the specific columns?

thank you

Could you explain with an example? Do you want to overwrite all of the fields
(including the Primary Key) in a record in TableA with the corresponding
record in TableB? If so, might it not be simpler to delete the matching
records and just run an Append?

 

[Dirk Goldgar]

CAVEAT: The Jet DB engine does not implement transactions, so if execution
is interrupted after the deletion and before insert, you will lose data.
This
mehtod is OK if Table A is on a DB engine that implements transactions
(e.g.
SQL Server, Oracle).

Jet supports transactions. What it doesn't support is multi-statement
stored procedures; however, in code you can begin a transaction, execute a
series of statements using the same connection, and then commit or rollback
the transaction.