开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜