INSERT SELECT WHERE insert multiple rows from constants

Question

I'm trying to insert multiple rows whose values are not taken from an existing table but provided from outside along with a where condition using INSERT ... SELECT ... WHERE.

The following query is not working:

mysql> insert into `my_table` SELECT 1 as a, 2 as b, 3 as c from dual UNION ALL SELECT 4 , 5 , 6 from dual UNION ALL SELECT 7 , 8 , 9  from dual where 1>2 ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from my_table;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 2 |    3 |
| 4 | 5 |    6 |
+---+---+------+
2 rows in set (0.00 sec)

I want to query to not insert any row because the where condition is false. But, the where clause is applied only for the last select and 1st 2 selects are not affected by where clause.

How can I correct it?

you can try with select * from (all your query ) where 1 >2 – krishn Patel Apr 03 '17 at 13:27 — krishn Patel, Apr 03 '17 at 13:27

score 3 · Accepted Answer · answered Apr 03 '17 at 13:28

3

Just wrap the UNION ALL subqueries in a subquery so as to apply WHERE to the whole result set:

insert into `my_table` 
select a, b, c 
from (
   SELECT 1 as a, 2 as b, 3 as c 
   from dual 

   UNION ALL 

   SELECT 4 , 5 , 6 
   from dual 

   UNION ALL 

   SELECT 7 , 8 , 9  
   from dual ) as t
where a > b ;

answered Apr 03 '17 at 13:28

Giorgos Betsos

71,379
9
63
98

1

@PankajSinghal you are aware that you need to accept the question, so the community can know that a solution was found right? – Masivuye Cokile Apr 03 '17 at 13:34
2

@MasivuyeCokile Yes, I'm aware of it. But, are you aware of the fact that I can't accept an answer unless it's 10 mins? – Pankaj Singhal Apr 03 '17 at 13:36
@PankajSinghal nope didn't know that sorry. – Masivuye Cokile Apr 03 '17 at 13:45

INSERT SELECT WHERE insert multiple rows from constants

1 Answers1