CODEWITHSUNDEEP
mysql
Stephen Bouffe
Stephen Bouffe

Reputation: 517

How to update two columns from one select

Can I combine this in to one?

UPDATE 1_packages k 
SET 
    rrp = (SELECT 
            SUM(u.quantity * p.rrp) * (sum(p.price_each <> 0 and p.rrp = 0) = 0)
        FROM
            1_packages_plu u
                INNER JOIN
            1_products p ON u.fk_products_id = p.id
        WHERE
            fk_packages_id = k.id)
;

UPDATE 1_packages k 
SET 
    rrp_inc = (SELECT 
            SUM(u.quantity * p.rrp_inc) * (sum(p.price_each <> 0 and p.rrp_inc = 0) = 0)
        FROM
            1_packages_plu u
                INNER JOIN
            1_products p ON u.fk_products_id = p.id
        WHERE
            fk_packages_id = k.id)
;

Upvotes: 0

Views: 127

Answers (2)

commit
commit

Reputation: 4807

It's simple, Try

UPDATE 1_packages k 
   SET rrp = (SELECT SUM(u.quantity * p.rrp) * (sum(p.price_each <> 0 and p.rrp = 0) = 0)
             FROM 1_packages_plu u INNER JOIN 1_products p ON u.fk_products_id = p.id 
             WHERE fk_packages_id = k.id),
       rrp_inc = (SELECT SUM(u.quantity * p.rrp_inc) * (sum(p.price_each <> 0 and p.rrp_inc = 0) = 0)
                 FROM 1_packages_plu u INNER JOIN 1_products p ON u.fk_products_id = p.id 
                 WHERE fk_packages_id = k.id)

Upvotes: 2

Pouki
Pouki

Reputation: 1664

Try :

UPDATE table_name 
SET column_one = "value one", 
    column_two = "value two"
WHERE condition

Upvotes: 0

Related Questions