skip to Main Content

I need to insert into a table (groupL) two rows (custId, aName). custId is constant while aName is multiple records of variable size based on a nested subquery.

That subquery is:

SELECT aName 
FROM artwork 
WHERE artwork.title IN (SELECT title 
                        FROM classify 
                        WHERE NEW.g_name = classify.g_name);

I’ve tried a lot of SQL but I really don’t get it. I’m looking for something that if we have two rows in aName (name1, name2) it’ll do something like

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name1 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name1);

INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name2 
    WHERE NOT EXISTS (SELECT custIdConstant, aName 
                      FROM groupL 
                      WHERE custId = custIdConstant AND aName = name2);

How do I do this? Any guidance would be appreciated.

2

Answers


  1. You can do in one Go but you need to have the ids before hand, if you want to compare and of course a FROM clause

    CREATE tABLe groupL(custId int , aName TEXT) 
    
    CREATE TABLE
    
    CREATE TABLe aName (name1 TEXT, name2 TEXT)
    
    CREATE TABLE
    
    INSERT INTO aName VALUEs ('A','B')
    
    INSERT 0 1
    
    DO $$
    DECLARE  
       custIdConstant integer := 10;  
    BEGIN  
    INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);
    
    INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);
    
    END $$;
    
    DO
    
    SELECT * FROM groupL
    
    custid aname
    10 A
    10 B
    SELECT 2
    
    DO $$
    DECLARE  
       custIdConstant integer := 10;  
    BEGIN  
    INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name1 FROM aName  WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name1);
    
    INSERT INTO groupL(custId, aName) 
    SELECT custIdConstant, name2 FROM aName WHERE NOT EXISTS (SELECT 1 FROM groupL WHERE custId = custIdConstant AND aName = name2);
    
    END $$;
    
    DO
    
    SELECT * FROM groupL
    
    custid aname
    10 A
    10 B
    SELECT 2
    

    fiddle

    Login or Signup to reply.
  2. I think you just want to check for the existence of those rows via a second subquery:

    INSERT INTO groupL (custId, aName)
    SELECT <custId>, a.aName 
    FROM artwork as a
    WHERE a.title IN (SELECT c.title 
                      FROM classify as c INNER JOIN NEW ON NEW.g_name = c.g_name)
      AND NOT EXISTS (SELECT 1
                      FROM groupL as g
                      WHERE g.custId = <custId> AND g.aName = a.aName);
    

    You might want to check into merge as well.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search