have one record overwrite another record?

sfullman

OK, here's a fun one..

I have two records in the same exact table, which contains a single primary key, say "ID".

OK, record 2 is a duplicate of record 1 (same person). but record 1 has outdated information, record 2 is current.

How can I update all fields in record 1 setting them to the corresponding fields of record 2? (Oh and the primary key of record 1 would remain the same however).

Yes I could write a script but wonder if SQL allows this via queries.

Sam

MrPixel

Originally posted by sfullman
OK, here's a fun one..

I have two records in the same exact table, which contains a single primary key, say "ID".

No, that's an impossible one. If it is a primary key it must also be unique (ie., cannot be duplicated): MySQL (or any RDBM worth it's salt) will generate an error in such a case. If this were not the case, it would defeat the whole purpose of having a primary key.

SELECT old info
UPDATE desired row
DELETE old row
.
.
.
OPTIMIZE table

[edit: fixed bad phpBB Code]

sfullman

What I meant was, the primary key consisted of one column, not that they had the same values -- maybe I misworded. i.e. One column called "ID" -- record one has value 1 for ID, record 2 has 2 for ID.

I want to replace all column values in 1 (except for the primary key column) with the column values in 2.

Sam

TheDefender

Hey Sam, MrPixel answered you...

You have to query the old, and use that info to update the new... MySQL doesn't have a way to do this. The script itself should be fairly easy for you tough.