gpt4 book ai didi

mysql - INSERT SELECT WHERE 从常量插入多行

转载 作者:行者123 更新时间:2023-11-29 01:50:59 25 4
gpt4 key购买 nike

我正在尝试插入多个行,这些行的值不是从现有表中获取的,而是使用 INSERT ... SELECT ... WHERE 从外部提供的以及 where 条件。

以下查询无效:

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)

我想查询不插入任何行,因为 where 条件为假。但是,where 子句仅适用于最后一个 select 和 1st 2 select 不受 where 的影响条款。

我该如何纠正它?

最佳答案

只需将 UNION ALL 子查询包装在一个子查询中,以便将 WHERE 应用于整个结果集:

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 ;

关于mysql - INSERT SELECT WHERE 从常量插入多行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43185879/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com