CODEWITHSUNDEEP
mysql
Thomas Williams
Thomas Williams

Reputation: 1546

Mysql insert only if conditions are met

Ok what I want to do is this. I only want to insert if the amount I am inserting, plus the sum of the column is not over my value eg 112. I am only summing one invoice amount which is where the sales invoice id comes in. So I am going to add an amount of £20.

So I basically need a query which says Insert Amount if Amount+"Sum of column" is smaller than or equal to the total quantity.

So here is my table layout

sipID|SalesInvoice_id|Date|Amount
1|116|07/11/2017|2|
2|115|07/11/2017|88|
3|116|07/11/2017|22|
7|115|07/11/2017|22|

So I thought an INSERT SELECT might work so I started getting the SELECT query to work first, and here is my working select query

SELECT SUM(`Amount`) FROM `salesinvoice_payments`
GROUP BY `SalesInvoice_id` HAVING SUM(`Amount`)+20 <= 112 AND `SalesInvoice_id`=115

Basically at the moment I am hard coding the amount I wish to add which is 20. So what this select statement is doing. It will only get a value if the SUM(Amount)+20 is smaller or equal to 112 which is my total. This works nicely.

So back to my original question. I want to insert only if the condition is true. I can't seem to get the right format for this to work eg

INSERT INTO `salesinvoice_payments` (`Amount`)VALUES (20)
SELECT SUM(`Amount`) FROM `salesinvoice_payments`
GROUP BY `SalesInvoice_id` HAVING SUM(`Amount`)+20 <= 100 AND `SalesInvoice_id`=115

Upvotes: 0

Views: 549

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

INSERT INTO ... SELECT is without the VALUES statement.

Query

INSERT INTO `salesinvoice_payments` (`Amount`)
SELECT 20 FROM `salesinvoice_payments`
GROUP BY `SalesInvoice_id` HAVING SUM(`Amount`) + 20 <= 100 AND `SalesInvoice_id`= 115

Upvotes: 3

Related Questions