copy or update from one table to another

[Dave Cullen]

I need to copy records from one table to another, and overwrite (update) the
target table data if the record already exists.

INSERT INTO destination SELECT * FROM source WHERE <condition> works if the
records don't already exist in the destination table, but fails if they
exist.

UPDATE fails if the records don't already exist. Plus it requires tediously
typing all the column names and data.

Is there any SQL statement that'd work in both cases?

Thanks

 

[Rick B]

Typically when you try to move records from one table to another, you are
not following normalized database procedures. What are the two tables? You
can normally accomplish this with an additional field in your one table.
For example, if you are trying to move a record from your "active" table to
your" archive" table, DON'T. Instead, add an "active" checkbox field, or an
"inactive" checkbox field, or an "inactive" date field.

If you have a table for the "South Office" and a table for the "North
Office", then you should, instead, have one table with a new "Office" field.

Post back more details if you need specific help normalizing your data.