Geek asks and answers

MYSQL INSERT IF SUMS> CONSTANT

I am trying to insert a record if the sum of 3 columns of a user from 2 tables exceeds a constant.

I searched everything, found that you cannot put user variables in IFs, WHEREetc. Found, you cannot put SUMin IFs, WHEREetc. I'm at a complete loss. Here is an example of my previous bad code before unsuccessfully trying to use SUMin WHEREs if this helps:

SELECT SUM(num1) INTO @mun1 FROM table1 WHERE user = '0';

SELECT SUM(num2) INTO @mun2 FROM table1 WHERE user = '0';

SELECT SUM(num3) INTO @mun3 FROM table2 WHERE column1 = 'd' AND user = '0';

SET @mun4 = @mun1 - @mun2 - @mun3;

INSERT INTO table2 (user, column1, column2) VALUES ('0', 'd', '100') WHERE @mun4 >= 100;
+5
source share
2 answers

Try the following:

INSERT INTO table2 (user, column1, column2) 
select '0', 'd', '100'
from dual
where (SELECT SUM(num1 + num2) FROM table1 WHERE user = '0') +
      (SELECT SUM(num3) FROM table2 WHERE column1 = 'd' AND user = '0') > 100;

This is a case of a general solution to the "insert if condition" problem:

insert into ... select ... where condition

, , , , false - , , , .

+4

, @Bohemian, LIMIT, , select

INSERT INTO table2 (user, column1, column2) 
SELECT      '0', 'd', '100'
   FROM dual
   WHERE 
         (SELECT SUM(num1 - num2) FROM table1 WHERE user = '0')
         (SELECT SUM(num3) FROM table2 WHERE column1 = 'd' AND user = '0') > 
         100 
   LIMIT 1
+1

More articles:


All Articles