CODEWITHSUNDEEP
mysql
half.italian
half.italian

Reputation: 21

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

Upvotes: 2

Views: 1524

Answers (2)

cdonner
cdonner

Reputation: 37698

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.

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60528

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.

Upvotes: 2

Related Questions