SQLite - update from view

0
votes

I have a view:

    CREATE VIEW UsersCounts AS SELECT
        users.id,
        users.username,
        users.email,
        SUM(CASE WHEN orders.id > 0 THEN 1 ELSE 0 END) as orders_all,
        SUM(CASE WHEN orders.status='ACTIVE' THEN 1 ELSE 0 END) as orders_active,
        SUM(CASE WHEN orders.status='RETURNED' THEN 1 ELSE 0 END) as orders_returned
    FROM users
        INNER JOIN orders ON
            users.email = orders.email
    GROUP BY users.id

Result:

id | username | email | orders_all | orders_active | orders_returned

How i can set Users.customer_status = "VIP"' where orders_active > 5?

3
sqlsqliteviewsql-updatedatabase-trigger
You want to show that in that view? Or do you want to update any table? The title of the question and the description leave room for interpretation... – sticky bit

3 Answers

1
votes

example:

update table from view id set age < 18 unable smoke

create table TestTable (id int,age int,smokable NVARCHAR(8));

CREATE VIEW TestView AS SELECT
id,
case when age < 18 then 'young' 
else 'old'
end status
from TestTable;

insert into TestTable (id,age,smokable) values (1,25,null),(2,17,null),(3,12,null),(4,9,null);

select * from TestView;

id | status
:- | :-----
1  | old   
2  | young 
3  | young 
4  | young 

select * from TestTable;

id | age | smokable
:- | :-- | :-------
1  | 25  | null    
2  | 17  | null    
3  | 12  | null    
4  | 9   | null    

update TestTable
set smokable = 'Unable'
where id in (select id from TestView where status = 'young');

select * from TestTable;

id | age | smokable
:- | :-- | :-------
1  | 25  | null    
2  | 17  | Unable  
3  | 12  | Unable  
4  | 9   | Unable
0
votes

For the update you could use 

    UPDATE users 
    SET customer_status = 'VIP'
    where EXISTS ( SELECT orders_active
                   FROM orders_active
                   WHERE  users.email = email
                   AND  orders_active > 5
    )
0
votes

You would do:

CREATE VIEW UsersCounts AS
    SELECT u.id, u.username, u.email,
           SUM(CASE WHEN o.id > 0 THEN 1 ELSE 0 END) as orders_all,
           SUM(CASE WHEN o.status = 'ACTIVE' THEN 1 ELSE 0 END) as orders_active,
           SUM(CASE WHEN o.status = 'RETURNED' THEN 1 ELSE 0 END) as orders_returned,
           (CASE WHEN SUM(CASE WHEN o.status = 'ACTIVE' THEN 1 ELSE 0 END) > 5
                 THEN 'VIP'
                 ELSE MAX(u.customer_status)
            END) as customer_status
    FROM users u INNER JOIN
         orders o
         ON u.email = o.email
    GROUP BY u.id