How to update multiple rows with one single query

Question

I use Postgresql + PHP.

Say I have this table:

Books ( id, title, year )

and this array of titles in PHP:

$titles = array ("bible","kafka","Book of Eli");

now I want update all rows where the title is in the $titles array above.

So I need a query like this:

UPDATE books SET year = '2001-11-11' WHERE title is in $titles;

Is is possible with one single query ? Or do I need to use FOR loop ?

Answer 1

It's possible, you were actually quite close.

For the SQL, the syntax looks like this:

UPDATE books SET year = '2001-11-11' WHERE title IN ('bible','kafka','Book of Eli');

To generate that using PHP, you'll want to do something like this:

$query = "UPDATE books SET year = '2001-11-11' WHERE title IN ('" . implode("','", $titles) . "');'";

The PHP implode() function joins array elements together using a string, so I put ',' between all of them, with the initial and final ' being put in the string manually.

Note that this will currently fail if any of the titles contain an apostrophe. If that's a possibility you will need to escape those.

Answer 2

You can use the implode() function, which will let you turn the array into a comma-separated string:

$titles = array ("bible","kafka","Book of Eli");
$comma_separated = implode(",", $array)
$sql = "UPDATE books SET year = '2001-11-11' WHERE title is in (" . $comma_separated .")"

Answer 3

What about

$sql = "UPDATE books SET year = '2001-11-11' WHERE title in ('".implode("','",$titles)."')";