0

I have a MySQL table with this configuration

I want to perform following operation:

let's assume a variable amount = some_value

for each row in ingridients_entry {
IF amount = 0
break;
END IF
IF amount > count - consumed
    SET amount = amount - (count-consumed) AND consumed = count
ELSE
    SET consumed = consumed + amount AND amount = 0
END IF
}

Is it possible with the sql UPDATE query?

I was thinking about using case with update in a procedure like this

DELIMITER $$
CREATE PROCEDURE consumeIngridients(IN amount FLOAT, IN sr INT)
BEGIN
update ingridients_entry set consumed=CASE
WHEN amount = 0 THEN consumed
WHEN amount > (count - consumed) THEN SET amount = amount - (count - consumed); count
ELSE consumed + amount;SET amount = 0;
END CASE;
where serial=sr and count>consumed;
END$$
DELIMITER ;

But it is giving me error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET amount = amount - (count - consumed); count ELSE consumed + amount;SET amoun' at line 5

View the schema here

Please help!

Ayush Jaiswal
  • 43
  • 1
  • 8
  • A `WHEN...THEN` statement returns a value, in this case the value that is set by `SET consumed=`. Do not use `SET` inside this statement. Also, do not have multiple expressions inside this statement (the ones separated by a semicolon--which by the way isn't your delimiter here since you set it to `$$`). Instead, let your `WHEN` statement return the value you want `consumed` to equal, and write the rest of your logic and assignments in an `IF` statement. – Anton Apr 14 '16 at 15:02
  • Ok, I'll try that! – Ayush Jaiswal Apr 14 '16 at 15:03
  • Please edit your post to include your table layout, rather than a link to an outside site. The link is broken, and even if it worked right now, it might go away in the future and would make your question useless to future readers. – Andy Lester Apr 14 '16 at 15:09
  • Schema is provided in the form of image at the bottom of question: [View the schema here](http://i.stack.imgur.com/hEHKK.png) – Ayush Jaiswal Apr 14 '16 at 15:22
  • Just to be clear I want to execute those operation simultaneously, i.e., with every update exeution and IF statement doen't work inside update query. @Anton – Ayush Jaiswal Apr 14 '16 at 15:24
  • My fault, I misunderstood your goal because I thought `amount` was a table field, and not a parameter. In your case, if you want to run an `UPDATE` query with some inline assignment and iteration logic, you can use user-defined variables. See https://dev.mysql.com/doc/refman/5.5/en/user-variables.html and an example at http://stackoverflow.com/questions/16644551/mysql-update-a-field-with-an-incrementing-variable – Anton Apr 14 '16 at 15:32
  • 1
    Thank you very much, Sir @Anton for your kind help. Finally, I made it work, although it required some evil logic :P. `SET @amount = 4.5; SET @debit = 0; update ingridients_entry set consumed=CASE WHEN @debit>0 THEN consumed+(@amount := 0)+(@debit := 0) WHEN @amount=0 THEN consumed WHEN @amount>=(count-consumed) THEN (@amount := @amount-(count-consumed))*0+count WHEN @amount<(count-consumed) THEN consumed+@amount+(@debit := 1)*0 END where serial=1 and count>consumed;` – Ayush Jaiswal Apr 14 '16 at 17:34

0 Answers0