SQL - How to insert into table based on id's found in another table?-CodePudding

I would need something like this pseudo code:

FOREACH USERID FOUND IN (SELECT DISTINCT USERID FROM USERAUTHORIZATIONS WHERE AUTHID <> 11)
 INSERT INTO USERAUTHORIZATIONS (USERID, AUTHID) VALUES (USERID, 11)

CodePudding user response：

I think you are probably looking for something like

INSERT INTO userauthorizations
 SELECT DISTINCT userid, 11
 FROM userauthorizations ua1
 WHERE NOT EXISTS 
  (SELECT *
  FROM userauthorizations ua2 
  WHERE ua2.userid = ua1.userid 
   AND ua2.authid = 11)

This will find all users who do not currently have a authid=11 and add a row for that userid with authid=11.

Note that this is different from your pseudocode (SELECT DISTINCT USERID FROM USERAUTHORIZATIONS WHERE AUTHID <> 11), which finds users who have at least one entry that isn't authid=11.