[Solved] Updating loyalty points status in SQL?

[sting5]

Due to emoployee profile permissions, orders, whose status was set to "shipped", did not get loyalty points as "validated". Could someone help me to set up the correct syntax for mass-changing loyalty points state to "2" instead of "1", if order was shipped? I need something like:

IF COLUMN "current_state" ROW value IN TABLE ps_orders" is "20" FOR id_order, then FIND ROW value in COLUMN "id_order" AND SET "id_loalty_state" value to "2"

 

If anyone could tell me where to find correct syntax, that would be great!

[Mukshi Sharma]

didn't get this 🙂

Edited July 23, 2019 by Mukshi Sharma (see edit history)

[sting5]

Sorry, not sure if I have explained it in the best way possible... We have around 2500 orders, where customers did not get their loyalty points validated - we need to edit the database table with mass query, so orders, which have status "shipped", would get their points validated. Problem is, that order status is in one table, while point status/validity is set in another table.

 

I've tried using this code, but no luck:

UPDATE ps_loyalty
SET    id_loyalty_state = '2',
WHERE  ps_orders.id_order = ps_loyalty.id_order
       AND ps_orders.current_state = '4' 

As I've said, I don't know sql programming language, thus not sure how to set the query right. Any help would be greatly appreciated..!

Edited July 24, 2019 by sting5 (see edit history)

[sting5]

Anyone? Please?

[sting5]

Thanks for Your help everyone - really appreciated!

I've managed to find the correct syntax. If anyone needs it, here is the full MySQL command line (1.6.x.x):

UPDATE ps_loyalty
SET id_loyalty_state = '2'
WHERE id_order IN (SELECT id_order FROM ps_orders WHERE current_state = '4')
AND id_loyalty_state = '1'

This will change loyalty points status to "available" for orders which are set as "shipped", if the points are for some reason "awaiting validation".