CODEWITHSUNDEEP
mysqlsql
Rob
Rob

Reputation: 6380

How to update a column that matches the criteria

I have the following query that is selecting particular data based on criteria:

SELECT p.customer_id
     , p.product_id
     , p.unit_price
     , c.updated_profile 
  FROM imp_subscriptionitems p
  LEFT 
  JOIN imp_customerdata c 
    ON p.customer_id=c.user_id
 WHERE c.updated_profile = 1
   AND p.product_id = 253

How can I update the p.unit_price column where the criteria matches and change it to 2?

Upvotes: 0

Views: 71

Answers (3)

Siva Koteswara Rao
Siva Koteswara Rao

Reputation: 129

UPDATE imp_subscriptionitems p

LEFT JOIN imp_customerdata c ON c.user_id = p.customer_id

SET p. unit_price =2

WHERE c.updated_profile ='1' AND p. product_id ='253'

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You can use the EXISTS to update it as follows:

UPDATE IMP_SUBSCRIPTIONITEMS P
   SET P.UNIT_PRICE = 2
 WHERE P.PRODUCT_ID = '253' 
   AND EXISTS ( SELECT 1
  FROM IMP_CUSTOMERDATA AS C WHERE P.CUSTOMER_ID = C.USER_ID
   AND C.UPDATED_PROFILE = '1' )

Note: Your original query is not doing what you are expecting. It is not doing LEFT OUTER JOIN instead it is doing INNER JOIN

You can also use multi-table update as follows:

update imp_subscriptionitems i,
(SELECT p.customer_id
     , p.product_id
     , p.unit_price
     , c.updated_profile 
     , p.pk_column -- replace pk_Column with actual primary key column name of imp_subscriptionitems 
  FROM imp_subscriptionitems p
  LEFT  JOIN imp_customerdata c 
    ON p.customer_id=c.user_id and c.updated_profile = 1
   where p.product_id = 253) derived_Data
set i.unit_price = 2
where p.pk_column = i.pk_column -- replace pk_Column with actual primary key column name of imp_subscriptionitems

And now direct update on the table using join is also possible. But that is already updated in another answer so now I am skipping the details for that in it.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

The direct translation of your current select query to an update would be an update join:

UPDATE imp_subscriptionitems p
INNER JOIN imp_customerdata c 
    ON p.customer_id = c.user_id
SET
    p.unit_price = 2
WHERE
    c.updated_profile = 1 AND
    p.product_id = 253;

Upvotes: 1

Related Questions