SQL ERROR - on Updating Column Value in First Table Based on Other Two Tables

I have 3 Tables OrderPayment, OrderSummaryInfo and OrderLossEntity.

OrderPayment table has a column OrdertypeCD, OrderLossEntity table has a column OrderType.

There is no direct relation between all the 3 tables.

Ordersummary has a primary key orderevententity. This table is the master table for both the tables (OrderPayment and OrderLossEntity).

OrderSummaryinfo table has a column called orderNumber and OrderPayment table also has OrderNumber. This OrderNumber is the primary key in OrderPayment table and is referring to OrderSummaryinfo:

OrderSummaryInfo.OrderNumber = OrderPayment.OrderNumber

OrderSummaryinfo.orderevententity is the primary key for OrderLosseventEntity.id --> where is the primary key in OrderLosseventEntity table referring to OrderSummaryinfo.orderevententity:

OrderSummaryinfo.orderevententity = OrderLossEntity.id

My requirement is OrderTypeCD has 3 possible values "Cash", "Gift" and "rewards".

OrderType column has 2 possible values - "Direct" and "Indirect".

Wherever OrderTypeCD has value "Rewards", I should change it to a new value "CashBack" for those orders which have OrderType="Indirect".

I am trying a query but I get an error

UPDATE Orderpayment op 
JOIN OrderSummaryInfo osi ON op.orderNumber = osi.OrderNumber 
JOIN OrderLossEntity ole ON ole.id = osi.orderevententity 
SET op.ordertypeCd = 'CashBck'  
WHERE op.orderTypeCD = 'Rewards' 
  AND ole.OrdeType = 'Indirect';

I get an error

> SQL State : 42601

Please help me correct this query.

Your first error is the sequence of clauses. The set clause must immediately follow the update and you cannot use table alias in the set (see documentation). So you need:

update OrderPayment  op 
   set OrderTypeCd = 'CashBck' ...

Secondly, the from portion must follow standard SQL format, so update table_name set column_name join will also give a syntax. To accomplish the necessary join you need a "sub select". Taking this and the above together along with your other stated requirements you get:

update OrderPayment  op 
   set OrderTypeCd = 'CashBck'  
  from (select  osi.OrderNumber 
        from OrderSummaryInfo osi
        join OrderLossEntity  ole 
          on ole.id = osi.OrderEventEntity
        where ole.OrderType = 'Indirect'
       ) jOrder
where op.OrderNumber = jOrder.OrderNumber
  and op.OrderTypeCd = 'Rewards';

NOTE: Not tested, no test data supplied.