Search code examples
sqlpostgresqljoinsql-update

Updating column value in a Table depends on other column value in another table SQL and PostgreSQL

there are two Table called OrderPayment and Ordersummaryinfo. OrderPayment has a column Called "orderNumber" and "paymentType". where as OrderSummaryinfo has columns "orderNumber","ProductCd".

This OrderNumber is the primary key in OrderPayment table and is referring to OrderSummaryinfo. OrderSummaryInfo.OrderNumber = OrderPayment.OrderNumber.

there are some paymentType's "CASH","REWARDS","GIFT" in OrderPayment Table.

There are some ProductCd's like "AUTO","NON_AUTO","AIR".

my Requirement is Wherever paymentType value is "CASH" i should change it to "REWARDS". whenever i ran "Update Orderpayment set paymentType='REWARDS' where Paymenttype="CASH" query it is doing for all of the Orders present in orderpayment table.

But i should the operation only for AUTO Products. SO how can i establish a link between these two tables . if possible any one can help me in this query . Thanks

Solution

  • Try this. I guess it should work now.

    UPDATE OrderPayment 
SET paymentType = 'REWARDS'
FROM OrderSummaryinfo
WHERE OrderPayment.orderNumber = OrderSummaryinfo.orderNumber
AND OrderPayment.paymentType = 'CASH'
AND OrderSummaryinfo.ProductCd = 'AUTO';