OK, this is sort of an addendum to the other "Update from one table to another" thread, but it's a little different so I thought that future readers might find it easier in a different thread.

I'm using this to copy matching rows from one table to another:

UPDATE t1, t2
SET t1.comment = t2.comment
WHERE t1.username = t2.username
AND t1.postdate = t2.postdate

Table t2 is an old backup of t1, so they have an identical structure.

Now, I'm wanting to modify the code to update like it is, but then when an update is made, delete the corresponding row from t2.

This way, t1 would be completely updated, and t2 would only have rows in it where no matching row was found in t1.

My thought was to add a dummy column to t2, then change the script to:

SET t1.comment = t2.comment, t2.dummy="1"

Then, after the query runs, a second query of:

DELETE FROM t2 WHERE dummy="1"

For the sake of knowledge and efficiency, though, is there a better / easier way to do this in a single query?

On 04-11-2011 04:55, Jason C wrote:

DELETE FROM t2
WHERE (username, postdate)
IN (SELECT username, postdate
FROM t1 WHERE t2.comment=t1.comment)


--
Luuk