CODEWITHSUNDEEP
mysqltriggers
Dao Lam
Dao Lam

Reputation: 2937

How to update multiple columns at once in MYSQL?

I looked up several ways to update multiple columns via SELECT but still unable to get it to work. This is what I tried:

delimiter @@
CREATE TRIGGER trigger_sales 
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE sales 
SET (userid, productid, transaction, price, quarter) =
SELECT userid, productid, COUNT(productid) AS transaction, SUM(total_P) AS price,
       case when (month(date)) >=3 and (month(date)) <=5 then 'Spring' 
            when (month(date)) >=6 and (month(date)) <=8 then 'Summer'
            when (month(date)) >=9 and (month(date)) <=11 then 'Fall' 
            else 'Winter'
            end AS quarter  
FROM orders     
GROUP BY userid, productid, quarter
END@@
delimiter;

Thank you!

Upvotes: 2

Views: 6550

Answers (2)

GregD
GregD

Reputation: 2877

I have tested your query without any sample data, but this solution could set you on the right track. I used VIEW to overcome the GROUP BY restriction while using UPDATE on multiple tables:

CREATE VIEW orders_view AS
     SELECT userid, 
            productid, 
            COUNT(productid) AS 'transaction', 
            SUM(total_P) AS price,
            CASE 
       WHEN (MONTH(o.date)) >=3 AND (MONTH(o.date)) <=5 THEN 'Spring' 
       WHEN (MONTH(o.date)) >=6 AND (MONTH(o.date)) <=8 THEN 'Summer'
       WHEN (MONTH(o.date)) >=9 AND (MONTH(o.date)) <=11 THEN 'Fall'
               ELSE 'Winter'
            END AS quarter    
       FROM orders     
   GROUP BY userid, productid, quarter;

     UPDATE sales s, orders_view o
        SET s.userid = o.userid,
            s.productid = o.productid,
            s.`transaction` = o.`transaction`,
            s.price = o.price,
            s.quarter = o.quarter
      WHERE s.userid = o.userid;

Please note that I haven't tested it with real data.

More information:

Upvotes: 3

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324820

My preferred way of doing something like this is to do an INSERT query, making sure that each insertion has a primary key that is already being used. Then I add ON DUPLICATE KEY UPDATE, go through all the fields in the form `column`=VALUES(`column`) and it's good to go. This usually works best with foreign keys in place.

Upvotes: 4

Related Questions