SQL reference current record ("this") in query
I have a table like:
id | NODE_ID | last_updated
1 | 4 | 11-29-2010 ...
where the last_updated column is set to update after any change to the row using "on update CURRENT_TIMESTAMP"
I need to update all of the records with a NODE开发者_运维知识库_ID of 4 to 5, but I want to leave the timestamp unchanged.
I'm thinking to do an update and reference the currently selected row in the query to manually set the timestamp...confusing...like this
update jobs set NODE_ID=4, last_updated = this.last_updated where NODE_ID = 5;
What can I use to replace the "this" in the query? If I can't, then whats the best way to do this sort of thing?
~Sean
PS. mysql Ver 14.12 Distrib 5.0.86, for redhat-linux-gnu (i686) using readline 5.1
This should do it:
update jobs set NODE_ID=4, last_updated = last_updated where NODE_ID = 5;
ETA: Originally I didn't think this would work since I thought the on update
constraint would be executed after you set the value, erasing your "change" (or lack thereof). But this leads me to believe that is not the case.
If this is a one-time update, you could remove the constraint before running it, then adding it when you are done. See here. I don't think what you want to do will work because Mysql might touch the timestamp after the query is evaluated.
精彩评论