sql command to subtract one item from the quantity

Question

I am new in my sql and can not really figure out how to do it. I have a two tables. trucks

 select * from trucks;
+----+---------+----------+--------+--------------+
| id | size    | quantity | status | customers_id |
+----+---------+----------+--------+--------------+
| 12 | 10_feet | 3        | active |            0 |
| 13 | 10_feet | 3        | active |            0 |
| 14 | 10_feet | 2        | active |            0 |
| 15 | 14_feet | 5        | active |            0 |
| 16 | 14_feet | 2        | active |            0 |
| 17 | 14_feet | 2        | active |            0 |
| 18 | 17_feet | 2        | active |            0 |
| 19 | 17_feet | 2        | active |            0 |
| 20 | 24_feet | 3        | active |            0 |
| 21 | 10_feet | 1        | active |            0 |
| 22 | 24_feet | 1        | active |            0 |
+----+---------+----------+--------+--------------+

and truck_customers

select * from truck_customers;
+----+--------+----------+--------+------------------+---------+------------+
| id | first  | last     | dl     | email            | size    | date_in    |
+----+--------+----------+--------+------------------+---------+------------+
|  3 | Poul   | Jons     | A13324 | poul@gmail.com   | 10_feet | 2013-11-30 |
|  4 | Poul   | Watson   | A23439 | watson@gmail.com | 10_feet | 2013-11-30 |
|  5 | Alex   | Snders   | A22    | Alex@gmail.com   | 17_feet | 2013-11-30 |
|  6 | santes | Garsia   | A18337 | Santes@gmail.com | 10_feet | 2013-11-30 |
|  7 | James  | Bond     | JB111  | Bond@gmail.com   | 10_feet | 2013-11-30 |
|  8 | John   | Travolta | G123   | Gohn@gmail.com   | 14_feet | 2013-11-30 |
+----+--------+----------+--------+------------------+---------+------------+

When entering information into truck_customers, it should automatically subtract one item from trucks table based on the size.

Using

$sql = " UPDATE trucks SET quantity = --  WHERE size = '$size'";

But it does not work.

I can send you my code if it is going to be easier to understand.

What are you trying to do with `quantity = --`? – Mosty Mostacho Dec 01 '13 at 18:00 — Mosty Mostacho, Dec 01 '13 at 18:00

Hanky Panky · Answer 1 · 2013-12-02T07:41:38.317

3

You're almost there.

UPDATE trucks SET quantity = quantity-1 WHERE size = '$size'

Fiddle

edited Dec 02 '13 at 07:41

answered Dec 01 '13 at 09:18

Hanky Panky

46,730
8
72
95

Unfortunately I tried this one before and it did not work. – user3047719 Dec 01 '13 at 17:57
Take a look at the answer to this identical question. is it possible your quantity field is not set to integer thus doesnt work well with numeric functions ? http://stackoverflow.com/questions/19271493/subtract-1-from-the-number-in-a-row-sql-query?rq=1 – scrowler Dec 01 '13 at 18:33
If that field is numeric, there is no reason this should not work – Hanky Panky Dec 02 '13 at 03:55
Just chanjed it to be INT instead of varchar but it is still not working. – user3047719 Dec 02 '13 at 07:05
ok what is the value of your variable `$size`? and does it not update anything? or behaves different to what you expect? – Hanky Panky Dec 02 '13 at 07:34
It seems to work just as expected: http://sqlfiddle.com/#!2/770b1b/1 – Hanky Panky Dec 02 '13 at 07:41
It should work, but probably I have something wrong befor or in my for loop. I totally got lost:(( – user3047719 Dec 02 '13 at 09:02

score 0 · Answer 2 · answered Dec 01 '13 at 18:30

Not only would your code not work, but you'd lose your WHERE conditions too...

-- this is a comment in SQL

http://dev.mysql.com/doc/refman/5.0/en/comments.html

You need to assign to original variable plus/minus increment, as Hanky Panky mentioned above.

sql command to subtract one item from the quantity

2 Answers2