SQL: update field value to itself Feb20 '08

Sometimes your application contains query update code that you don't wish to modify, or that you don't have access to modify.

For example, using PHP:

UPDATE table SET field1 = " . $value1 . ", field2 = " . $value2 . " WHERE id = " . $id . ";

You may still have access to modify the values that get used in the query - in this case, $value1 and $value2.

Sometimes you don't want to update a particular field with a new value - you'd like to keep it as it is. However, despite whether or not a value should be something new, the UPDATE query still runs, so you need some way of telling the query to leave a particular field value as is.

The easiest way to avoid updating a field is to simply exclude it from the query:

UPDATE table SET field2 = " . $value2 . " WHERE id = " . $id . ";

In this case, we removed the instructions to update field1. If we don't tell it to update field1, it leaves the value as is.

But remember - we don't want to modify the query instructions, or we may not have access to modify it. The instructions run regardless of fields that should or should not get updated.

For the field that needs to remain the same, you could set it's value to itself. Our modified query would look like:

UPDATE table SET field1 = field1, field2 = " . $value2 . " WHERE id = " . $id . ";

Notice field1 is set to itself:

field1 = field1

No need to adjust the query instructions. The field value remains the same.

Categories: PHP , SQL , Tips

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

Or you can leave field1 out of the query and it won't be touched. ... Read more.

Popular Pages

  1. Fast rounded corners in Photoshop (7424 recent visits)
  2. PHP – passing variables across pages (2558 recent visits)
  3. JavaScript set selected on load (2413 recent visits)
  4. Removing all child nodes from an element (1828 recent visits)
  5. Firefox 3 smart address bar: wildcard search (1755 recent visits)
  6. iPod songs out of order? (1314 recent visits)
  7. Britney - Everytime piano tab (1138 recent visits)
  8. MySQL LEFT JOIN syntax (884 recent visits)
  9. Firefox 3 smart address bar: wildcard search (722 recent visits)
  10. Date difference in MySQL (651 recent visits)

Similar Entries

Stats

156 unique visits since July 2008

Syndicate

Advertisements