Arunendra Arunendra - 4 months ago 10
SQL Question

How to update records based on condition?

I have a table

tbl_loyaltypoints
in this table a column
status = 2
has default value

Now I have following Query to get all records

select lp.order_id, DATEDIFF(CURDATE(), FROM_UNIXTIME(lp.created_at)) as createon,
oi.deal_id, wo.returnWithin, lp.status
from tbl_loyaltypoints lp
inner join tbl_orders ord on lp.order_id = ord.id
inner join tbl_order_item oi on lp.order_id = oi.order_id
inner join tbl_workorders wo on oi.deal_id = wo.id
where ord.order_type = 1
order by lp.id DESC;


Output:

order_id createon deal_id returnWithin status
1045 4 160 20 2
1044 4 160 20 2
1043 20 160 20 2


I want to update status
tbl_loyaltypoints.status
when
createon==returnWithin
.

Is there any way to do this using Mysql?

Answer

You can transform the select into a join:

update tbl_loyaltypoints lp inner join
       tbl_orders ord
       on lp.order_id = ord.id inner join
       tbl_order_item oi
       on lp.order_id = oi.order_id inner join
       tbl_workorders wo
       on oi.deal_id = wo.id 
    set lp.status = ??
where ord.order_type = 1 and
      DATEDIFF(CURDATE(), FROM_UNIXTIME(lp.created_at)) = wo.returnWithin;

You don't specify what the new status is. The ?? is a placeholder.