CODEWITHSUNDEEP
mysql
skobaljic
skobaljic

Reputation: 9644

Join tables and update column on multiple conditions

I have two tables customers and orders, where important columns are:

/* Customers: */
id        | login     | membershipid
100       | email1    | 0
101       | email2    | 0

/* Orders: */
userid    | total
100       | 150
101       | 120
101       | 450

I want to update membershipid depending of total purchases for each customer. Lets say it will be zero if the sum is less than 500, one if sum is 500-1000, two if sum is over 1000.

I get the sum this way:

SELECT customers.login, sum( orders.total ) AS total_purchased 
FROM xcart_customers AS customers
LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
GROUP BY customers.login

but I am not sure how to update back customers table. Something like:

UPDATE xcart_customers set (membershipid) = (
    SELECT sum( orders.total ) AS total_purchased 
    FROM xcart_customers AS customers
    LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
    GROUP BY customers.login
)

but where and how to use conditions (and will GROUP BY work?):

CASE 
    WHEN 
    ..
    ELSE 
END

Upvotes: 1

Views: 33

Answers (2)

juergen d
juergen d

Reputation: 204864

UPDATE xcart_customers 
join
(
    SELECT customers.login, sum( orders.total ) AS total_purchased 
    FROM xcart_customers AS customers
    LEFT JOIN xcart_orders AS orders ON customers.id = orders.userid
    GROUP BY customers.login
) tmp on tmp.login = xcart_customers.login
set xcart_customers.membershipid = case when total_purchased is null or total_purchased < 500 then 0
                                        when total_purchased between 500 and 1000 then 1
                                        else 2
                                   end

Upvotes: 2

spencer7593
spencer7593

Reputation: 108470

One way to do this is with a join operation. Take your query, and turn it into an inline view, and join that back to the customers table.

We might want to handle the case when total_purchased is NULL as being less than 500 (when a customer doesn't have any related rows in xcart_orders.)

As a SELECT it would look like this:

SELECT t.login
     , t.membershipid
     , CASE 
         WHEN IFNULL(s.total_purchased,0) < 500 THEN 0
         WHEN s.total_purchased <= 1000 THEN 1
         WHEN s.total_purchased > 1000 THEN 2
         ELSE NULL
       END AS new_membershipid
  FROM customers t
  JOIN ( SELECT c.login
              , SUM(o.total) AS total_purchased 
           FROM xcart_customers c
           LEFT 
           JOIN xcart_orders o 
             ON o.userid = c.id 
          GROUP BY c.login
       ) s
    ON s.login = t.login

To convert that into an UPDATE, replace the SELECT ... FROM with UPDATE

and add a SET clause at the end of the statement (before the WHERE clause if there was a WHERE clause).

For example:

UPDATE customers t
  JOIN ( SELECT c.login
              , SUM(o.total) AS total_purchased 
           FROM xcart_customers c
           LEFT 
           JOIN xcart_orders o 
             ON o.userid = c.id 
          GROUP BY c.login
       ) s
    ON s.login = t.login
   SET t.membershipid =
       CASE 
         WHEN IFNULL(s.total_purchased,0) < 500 THEN 0
         WHEN s.total_purchased <= 1000 THEN 1
         WHEN s.total_purchased > 1000 THEN 2
         ELSE NULL
       END

NOTE: This assumes that login is UNIQUE` in the customers table.

FOLLOWUP

Given that membershipid cannot be assigned a NULL value, we can tweak the CASE expression. Do the check for > 1000 first, then a check for >= 500, else 0.

For example:

       CASE 
         WHEN s.total_purchased > 1000 THEN 2
         WHEN s.total_purchased >= 500 THEN 1
         ELSE 0
       END

Upvotes: 1

Related Questions