I want to change different column values of a table in one query, is that possible?
I tried something like this (just a guess):
<?php
$q="UPDATE tab SET name='samit' WHERE id='1' && set name='anju' where id='4'";
$run=mysql_query($q);
if($run){
echo 'updated';
}
else{
echo 'update failed';
}
?>
It's not working. I can do this using a loop, but a loop will increase operation time.
You can, but you'll have to use case like so:
UPDATE tab
SET name = CASE id
WHEN 1 THEN 'samit'
WHEN 4 THEN 'anju'
END
WHERE id IN (1,4);
Still, i'd recommend against it and just run your queries with a loop; also, mysql_* is deprecated.
It's propably not the best way but:
UPDATE tab
SET
name= IF(id='1', 'samit', name),
name= IF(id='4', 'anju', name)
UPDATE `tab`
IF(id= 1, "SET name= 'samit'", ''),
IF(id= 4, "SET name= 'anju'", '')
I hope this would work for you. Although i have not tested it yet but you can use if condition in update query
