2

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

half.italian
  • 21
  • 3

2 Answers2

2

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • So I guess there is an inherent reference to "this" record in the query. That should have been obvious since you basically always refer to the same (current) record in "where" clauses. Thanks all! – half.italian Nov 18 '10 at 21:46
1

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.

Community
  • 1
  • 1
cdonner
  • 37,019
  • 22
  • 105
  • 153