Home PageArchiveContact UsAbout Us
If you are hopelessly addicted to drupal;
if bringing yourself to bed is allways a strugle;
if your main source of entertainement
is watching lullabot videos; then you belog
here.

How to update multiple fields in multiple rows in a single MySQL query.

There is one thing that every web developer should always be mindful of. I am talking about code performance.

PHP & MySQL can be a powerful combination. However, if you are not careful, Drupal can make it very easy for you to write bloated and slow code.

One of the things that can seriously slowdown your code would be to make dozens of MySQL query requests while generating your PHP web page.

With a little ingenuity, though, you can do a lot with just a single MySQL query.
The following examples would work on a Drupal 6 database.
On this first MySQL query example, we show you how to update all records that are associated with the provided NIDs.

UPDATE node_revisions SET
title = CASE nid
WHEN 123 THEN "This is the 123 title"
WHEN 124 THEN "This is the 124 title"
END,
body = CASE nid
WHEN 123 THEN "This is the 123 body"
WHEN 124 THEN "This is the 124 body"
END,
uid = CASE nid
WHEN 123 THEN "1"
WHEN 124 THEN "1"
END
WHERE nid IN (123,124)';


The following query example is much more complex. It will update information to the database based on two criteria. The same schema can be applied so the record can be required to comply with three or more criteria in order for the data to be updated.

UPDATE node_revisions SET
title = (CASE
WHEN (nid = 123 AND uid = 1) THEN "This is the 123 title"
WHEN (nid = 124 AND uid = 1) THEN "This is the 124 title"
END),
body = (CASE
WHEN (nid = 123 AND uid = 1) THEN "This is the 123 body"
WHEN (nid = 124 AND uid = 1) THEN "This is the 124 body"
END)
WHERE (nid IN (123,124) AND uid = 1)';
No votes yet
MySQL