Loop in INSERT contition

user_1879838 0
Viewed 66

I am new in sql and it is not clear form me how I can do next: I have code:

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)

I need change this query to run also condition AND table2.type = 'admin', which means table2.val1, table2.val1 will be different, also condition AND table2.size1 > 4 should be changes to AND table2.size2 > 2

Of course I can run this query 2 times with different value, but maybe it is possible to do in one query

postgresql sql
2 Answers

You can simply modify the WHERE clause to achieve this in one pass:

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)
         )
user_138083190

Maybe it may not really good way, but you can run these two queries at the same time with 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
user_91049420
Related