insert contition中的sql循环

yfjy0ee7 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(219)

我是sql新手，不清楚下一步该怎么做：我有代码：

INSERT INTO table1 (val1, val2)
   SELECT table2.val1, table2.val2
FROM table2
   WHERE NOT EXISTS (
   SELECT count FROM table_log 
   WHERE event_id = table2.id)
   AND table2.type = 'user' -- AND table2.type = 'admin'
   AND table2.size1 > 4 -- AND table2.size2 > 2 (if user admin)

我需要更改此查询以同时运行条件 AND table2.type = 'admin' ，这意味着 table2.val1, table2.val1 会有所不同，也会有所不同 AND table2.size1 > 4 应更改为 AND table2.size2 > 2 当然，我可以用不同的值运行这个查询两次，但也许可以在一个查询中运行

sql postgresql

来源：https://stackoverflow.com/questions/63074887/loop-in-insert-contition

2条答案

按热度按时间

sg2wtvxw1#

您只需修改 WHERE 条款一次完成：

INSERT INTO table1 (val1, val2)
   SELECT table2.val1, table2.val2
FROM table2
   WHERE NOT EXISTS (
   SELECT count FROM table_log 
   WHERE event_id = table2.id)
     AND (   (table2.type = 'user' AND table2.size1 > 4)
          OR (table2.type = 'admin' AND table2.size2 > 2)
         )

赞(0）回复(0）举报 2021-07-24

yc0p9oo02#

也许这不是很好的方法，但是您可以使用同时运行这两个查询 common table expressions (cte) ```
WITH cte AS (
INSERT INTO table1 (val1, val2)
SELECT table2.val1, table2.val2
FROM table2
WHERE NOT EXISTS (
SELECT count FROM table_log
WHERE event_id = table2.id)
AND table2.type = 'user' -- AND table2.type = 'admin'
AND table2.size1 > 4
) INSERT INTO table1 (val1, val2)
SELECT table2.val1, table2.val2
FROM table2
WHERE NOT EXISTS (
SELECT count FROM table_log
WHERE event_id = table2.id)
AND table2.type = 'admin'
AND table2.size1 > 2

赞(0）回复(0）举报 2021-07-24

我来回答

insert contition中的sql循环

2条答案

相关问题

热门标签

最新问答