Insert query with condition

Question

I am trying to create an SQL query wherein I need to check to sum of columns from 2 tables and then insert a new row into another table if the condition is satisfied. I know I can use a stored procedure with explicit locking but want to know is it is possible to do it in a single SQL query. I wrote the following but it gives syntax error in MySQL workbench

INSERT INTO table1 (`col1`, `col2`) 
VALUES ('abc', 'xyz') WHERE 
(
((select 
COUNT(*) from table2 t2 where t2.id = 1)
+ (select sum(t3.counter) from table3 t3 
WHERE t3.id = 1)) < (select t4.total_allowed 
FROM table4 t4 where t4.id = 1)
);

score 3 · Accepted Answer · edited Apr 04 '14 at 15:33

3

This can be done because you can insert the results of a select query.

INSERT INTO table1 (`col1`, `col2`) 
SELECT 'abc', 'xyz'
FROM DUAL WHERE 
(
((select 
COUNT(*) from table2 t2 where t2.id = 1)
+ (select sum(t3.counter) from table3 t3 
WHERE t3.id = 1)) < (select t4.total_allowed 
FROM table4 t4 where t4.id = 1)
);

edited Apr 04 '14 at 15:33

user2009750

3,169
5
35
58

answered Apr 04 '14 at 15:26

Vulcronos

3,428
3
16
24

You beat me to the punch. I'll add the following as reference. [click here](http://stackoverflow.com/questions/5253302/insert-into-select-for-all-mysql-columns). – Tiny Haitian Apr 04 '14 at 15:27

score 0 · Answer 2 · answered Apr 04 '14 at 15:15

0

MySQL INSERT Syntax does not support the WHERE clause.

answered Apr 04 '14 at 15:15

user2009750

3,169
5
35
58

Then is there any other way to check for this condition in an SQL statement in MySQL? – Rahul Apr 04 '14 at 15:17
I am afraid you'll have to use multiple queries. – user2009750 Apr 04 '14 at 15:19
But won't multiple queries suffer from race condition? (assuming I am using JDBC to execute the queries in single transaction). Is stored procedure the only way to avoid this? – Rahul Apr 04 '14 at 15:20
Look if REPLACE query gets your job done, in transaction it shouldn't create any problem – user2009750 Apr 04 '14 at 15:25
but i think REPLACE is probably not your solution. – user2009750 Apr 04 '14 at 15:26

Insert query with condition

2 Answers2