CODEWITHSUNDEEP
mysqlsql
jogesh_pi
jogesh_pi

Reputation: 9782

Update Same mysql field twice in single query

I am not sure if its possible or not, Just want to know if it is. I have column plan_popular which has default value 0. Lets same i have a list : 

Plan Name | plan_popular  |  amount
===================================
plan A           0           25.00
plan B           1           50.00
plan C           0           90.00

This is how i am doing: 

$stmt = "update {CI}plans set plan_popular = 0";
$this->db->query($stmt);

$stmt2 = "update {CI}plans set plan_popular = 1 where plan_id = ?";
$this->db->query( $stmt2, array($plan_id) );

Now i have set the plan C to make. Now i want to reset it and want to make popular plan C to 1. What i am doing is running two queries, One i reset and make the plan_popular 0 and the second is get the update the plan C to 1 with there id. Is it possible in single query?

Upvotes: 0

Views: 828

Answers (3)

TommCatt
TommCatt

Reputation: 5636

Updates can be expensive, what with manipulating locks, triggers and constraints firing, etc. In general, you want to avoid updating a field to the same value it already has. In English, if plan_id = variable and plan_popular is 0 then set it to 1 but if plan_id is any other value and plan_popular is 1 then set it to 0.

UPDATE {CI}Plans
    SET plan_popular = if( plan_id = ?, 1, 0 )
where (plan_id = ? and plan_popular = 0)
   or (plan_id <> ? and plan_popular = 1);

The where clause lets through only those rows that will actually be changed by the update. If this is a largish table, that can make quite a difference in response time. Logic is much less expensive than any actual operation that can performed in the database.

Upvotes: 0

Domain
Domain

Reputation: 11808

try this,

UPDATE {CI}plans
   SET `plan_popular` = CASE `Plan Name` 
                      WHEN 'plan C' THEN 1 
                      ELSE 0
                      END
 WHERE `Plan Name` IN((select `Plan Name` from {CI}plans where plan_popular=1 ) , 'plan C');

Upvotes: 1

Barmar
Barmar

Reputation: 782785

You can use an expression to determine the value to assign:

UPDATE {CI}plans
SET plan_popular = IF(plan_id = ?, 1, 0);

Upvotes: 1

Related Questions