UPDATE whole row in Table A FROM Table B

  • Thread starter Thread starter juvi
  • Start date Start date
J

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
 
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).
 
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
Click to expand...

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?
 
tedmi said:
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).
Click to expand...


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.
 
Back
Top