MySQL Bugs: #49971: Allow INSERT .... WHERE ....

Bug #49971	Allow INSERT .... WHERE ....
Submitted:	29 Dec 2009 9:38	Modified:	7 Jan 2010 9:09
Reporter:	Ariel S	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, SQL, where

Description:
Allow command of the form:

INSERT INTO ... WHERE ....

This will run the insert only if the WHERE is true.

I know that I can convert this to

INSERT INTO ... SELECT ... FROM dual WHERE ....

But it would be nicer to be able to use simpler language.

How to repeat:
N/A

What is the point of this feature? If you are inserting one row:

insert into ... values(...)

you know for sure what values you are inserting. Why do you need where?

If you inserting data that you select from other table(s), then WHERE is already available. Please, provide some example that shows when this new feature will be useful.

It makes things easier. It's a convenience feature.

Yes, there are ways to work around it, for example I could do a select first, check if the where is true. Then do the insert or not. i.e. let the "parent" language do the logic. Or convert the insert into a insert ... select ...

But sometimes that is just not convenient. This is a feature request, not a bug report.

In my case one part of the application is doing, or not doing, some inserts.

Later I create an entry linking to that data. But I only want to create that link if there is data. But the data creation step is removed from this step. The only way for me to know if the data exists is to check it first.

So I'd have to do a select, see if it's true, then do the insert. This works, but would be simpler if I could just do it in SQL.

Second example:

You have a script that downloads data and inserts it. Then at the end of the script you want to create an entry in the log saying you got data. You only want the entry if there was data. But the scripting language makes it pretty hard to do a select and see the results (it's just a bunch of commands sent to the mysql client). Would be much easier if the insert could do it. Yes, you can convert the insert to an insert...select. Remember it's a feature request, it's not a bug report. I know I can do it in another way.

It seems like it would be something very simple to add, so I asked for it.

Many thanks for writing a feature request.

Unfortunately, I am not able to follow you.

CREATE TABLE t1(i integer);
CREATE TABLE t2(j integer);
INSERT INTO t1(i) VALUES(1),(2),(3),(4),(5);
INSERT INTO t2(i) VALUES(4),(5),(6),(7),(8);

INSERT INTO t2(j) SELECT i FROM t1 WHERE i>3;
INSERT INTO t2(j) SELECT j FROM t1,t2 WHERE t2.j = t1.i; 

And you suggest now:

INSERT INTO t2(j) WHERE j WHAT?

Maybe this?
INSERT INTO t1, t2(j) WHERE t2.j=t1.i;

Oh, there are much more complicated SQL queries you can write.

INSERT INTO t2 VALUES (4) WHERE EXISTS (SELECT * FROM t1 WHERE i < 4) OR (SELECT SUM(i) FROM t1) > (SELECT AVG(i) FROM t1)

I'm inserting a constant, but the where is complicated.

I'm fully aware I can convert this to:

INSERT INTO t2 SELECT 4 FROM dual WHERE EXISTS (SELECT * FROM t1 WHERE i < 4) OR (SELECT SUM(i) FROM t1) > (SELECT AVG(i) FROM t1)

But I really hate using dual in this context (and it's too bad I can't write it without using dual, even if insert doesn't get this new feature - but that's a separate request).

Or I can do:

SELECT EXISTS (SELECT * FROM t1 WHERE i < 4) OR (SELECT SUM(i) FROM t1) > (SELECT AVG(i) FROM t1) as bool

Then, outside MySQL check if bool is true, and then run the insert.

But you don't always have an outside language to work with.

Many thanks for writing a feature request.

Your request is neither SQL standard conform nor will we implement it.

Let me give you some solutions for your examples:

INSERT INTO t2 
SELECT 4 FROM dual
WHERE EXISTS (SELECT * FROM t1 WHERE i < 4) OR (SELECT
SUM(i) FROM t1) > (SELECT AVG(i) FROM t1)

Better:
INSERT INTO t2
SELECT 4 FROM t1
WHERE ((i < 4)
OR ((SELECT SUM(i) FROM t1) > (SELECT AVG(i) FROM t1)))

SELECT EXISTS (SELECT * FROM t1 WHERE i < 4) OR (SELECT SUM(i) FROM t1) > (SELECT AVG(i)
FROM t1) as bool

Better:
SELECT COUNT(*) FROM t1
WHERE ((i < 4)
OR ((SELECT SUM(i) FROM t1) > (SELECT AVG(i) FROM t1)))

If result > 0 then TRUE.

Additionally, you could work with CASE or IF:

http://dev.mysql.com/doc/refman/5.1/de/control-flow-functions.html

Your first rewritten query does not do the same thing as mine. If t1 has more than one row where i < 4 it will insert more than one row. Mine will only insert a single row.

Your second rewritten query is slower for no gain. Exists is a faster command than count(*).

And please remember these are just made up examples. Real queries will be more complicated.

This still seems like a useful addition if only to avoid the need for using the 'dual' table.