CODEWITHSUNDEEP
phpmysqlsqlsql-update
healer
healer

Reputation: 93

Update another column during a update query based on its new value

I have a table named item_quantity with 2 fields, quantity and requested. I have a query which will update the field quantity every time a customer buy. The problem is i want to update the column requested to 0 when the quantity becomes 0. This is what i tried so far.

UPDATE `item_quantity`SET `quantity`=quantity-5,`requested`= CASE when `quantity` = 0 then 0 else 1 end where `item_id`=1;

But the case is underlined red. I don't know if its syntax or logical error. How will i do this? Thank you in advance! [This is what i got]1

Upvotes: 0

Views: 38

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can do the update requested after you have set item_quantity. Then you can directly compare item_quantity like this:

UPDATE `item_quantity`
SET
  `quantity`= `quantity` - 5
  ,`requested`= IF(`quantity` = 0, 0, `requested`);

sample

mysql> SELECT * FROM item_quantity;
+----+----------+-----------+
| id | quantity | requested |
+----+----------+-----------+
|  1 |       10 |         9 |
+----+----------+-----------+
1 row in set (0,00 sec)

mysql> UPDATE `item_quantity`
    -> SET
    ->   `quantity`= `quantity` - 5
    ->   ,`requested`= IF(`quantity` = 0, 0, `requested`);
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM item_quantity;
+----+----------+-----------+
| id | quantity | requested |
+----+----------+-----------+
|  1 |        5 |         9 |
+----+----------+-----------+
1 row in set (0,00 sec)

mysql> UPDATE `item_quantity`
    -> SET
    ->   `quantity`= `quantity` - 5
    ->   ,`requested`= IF(`quantity` = 0, 0, `requested`);
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM item_quantity;
+----+----------+-----------+
| id | quantity | requested |
+----+----------+-----------+
|  1 |        0 |         0 |
+----+----------+-----------+
1 row in set (0,00 sec)

mysql>

Upvotes: 1

Gino
Gino

Reputation: 779

You can use a BEFORE UPDATE trigger. This trigger will fire just before the update will execute, and you can update the other field. Try the following:

CREATE TRIGGER item_quantity_before_update
BEFORE UPDATE
   ON item_quantity FOR EACH ROW
BEGIN
   DECLARE old_quantity NUMBER;
   DECLARE new_quantity NUMBER;

   SELECT quantity INTO old_quantity;

   SET new_quantity = old_quantity - 5;
   IF new_quantity == 0 THEN
      SET new.requested = 0
   ELSE
      SET new.requested = 1;
   END IF;
END; //

Upvotes: 0

Related Questions