Why is it not working? it's showing a error before else-CodePudding

this is the code:

    -- create
    CREATE TABLE I_UsrAccessRvw (
      empId INTEGER PRIMARY KEY,
      username TEXT NOT NULL,
      designation TEXT NOT NULL,
      roles TEXT NOT NULL,
      accesses TEXT NOT NULL
    );
    
    -- insert
    INSERT INTO I_UsrAccessRvw VALUES (0001, 'Clark','President', 'admin','edit delete review');
    INSERT INTO I_UsrAccessRvw VALUES (0002, 'Dave','sales rep', 'Operational role','review');
    INSERT INTO I_UsrAccessRvw VALUES (0003, 'Ava','finance manager', 'Managerial role','edit delete review');
    
    -- fetch 
    SELECT * FROM I_UsrAccessRvw;
    
    --mastertable
    CREATE TABLE CONDITION (
      roles TEXT NOT NULL,
      accesses TEXT NOT NULL,
      permission TEXT NOT NULL
    );
    
    --insertintomastertable
    INSERT INTO CONDITION VALUES ('admin','edit delete review','granted');
    INSERT INTO CONDITION VALUES ('Managerial role','edit review','granted');
    INSERT INTO CONDITION VALUES ('Operational role','review','granted');
    SELECT * FROM CONDITION;
    
    ALTER TABLE I_UsrAccessRvw
    ADD COLUMN permission TEXT;
    
    UPDATE 
      I_UsrAccessRvw p
      
    SET 
        permission = c.permission
    FROM 
        I_UsrAccessRvw e, CONDITION c
    WHERE 
       e.roles=c.roles and e.accesses= c.accesses and p.empId=e.empid;
    
    ELSE
    SET 
        permission AS "REVOKE"
       
      
       SELECT * FROM I_UsrAccessRvw order by empid;

CodePudding user response：

INSERT INTO I_UsrAccessRvw VALUES (0001, 'Clark','President', 'admin','edit delete review');
INSERT INTO I_UsrAccessRvw VALUES (0002, 'Dave','sales rep', 'Operational role','review');
INSERT INTO I_UsrAccessRvw VALUES (0003, 'Ava','finance manager', 'Managerial role','edit delete review');

-- fetch 
SELECT * FROM I_UsrAccessRvw;

--mastertable
CREATE TABLE CONDITION (
                           roles TEXT NOT NULL,
                           accesses TEXT NOT NULL,
                           permission TEXT NOT NULL
);

--insertintomastertable
INSERT INTO CONDITION VALUES ('admin','edit delete review','granted');
INSERT INTO CONDITION VALUES ('Managerial role','edit review','granted');
INSERT INTO CONDITION VALUES ('Operational role','review','granted');
SELECT * FROM CONDITION;

ALTER TABLE I_UsrAccessRvw
    ADD COLUMN permission TEXT;

UPDATE
    I_UsrAccessRvw p

SET
    permission = coalesce(c.permission,'REVOKE')
FROM
    I_UsrAccessRvw e, CONDITION c
WHERE
        e.roles=c.roles and e.accesses= c.accesses and p.empId=e.empid

SELECT * FROM I_UsrAccessRvw order by empid;

i think this is what u want to do, else keyword is not used like that.

CodePudding user response：

There is no else condition in SQL (with exception of case expression). What you can do is create an assignment CTE using a left outer join. Then update with the main query. (see demo)

with permission_set( rctid, permission) as 
     ( select p.ctid, coalesce(c.permission, 'REVOKE')
         from i_usraccessrvw p
         left join condition c 
                on ( (c.roles,c.accesses) = (p.roles,p.accesses) ) 
     )
update i_usraccessrvw i  
   set permission = (select permission 
                       from permission_set ps
                      where i.ctid = ps.rctid
                    );

NOTE: You need to learn the new modern join syntax (only 30 years old). The structure used from I_UsrAccessRvw e, CONDITION c where ... is an inner join. If the where clause does not match for every row the unmatched rows are simply not processed.