-1

I want firstly antire def column set to be 0 except the row where id is a variable (this should be 1). 

$sql = "update rolls set def = 0, def = 1 where id = :aid";
$st = $db->prepare($sql);
$st->execute([":aid" => $id]);

result - the first part - set to 0 - doesn't work;

Any help?

qadenza
  • 9,025
  • 18
  • 73
  • 126

1 Answers1

1

Separate into 2 queries

// Query one ; query two
$sql = "update rolls set def = 0; update rolls set def = 1 where id = :aid";
$st = $db->prepare($sql);
$st->execute([":aid" => $id]);
Triby
  • 1,739
  • 1
  • 16
  • 18
  • are you sure there is no `single query` way? – qadenza Feb 15 '20 at 06:24
  • Not really sure, I've never tried to update a table this way. Searching for `mysql update subquery` didn't get useful results. – Triby Feb 15 '20 at 06:27
  • Maybe this could be what you're looking for: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Triby Feb 15 '20 at 06:29
  • 1
    yes, it works - `$sql = "update rolls set def = 0; update rolls set def = 1 where id = :aid;";` - you should update your answer – qadenza Feb 15 '20 at 06:39