I am having a table "sales" With columns tranid and date

The next table is "order"

Tranid  orderid  orddate
1100    aa1      2022-06-01
1101    aa2      2022-06-02
1104    aa4      2022-06-09

Now I want to update the date column which is null with column date of order. So that the final results looks like this.

Tranid  trancode  date
 
1100    aa1      2022-06-01
1101    aa2      2022-06-02

This is by matching tranid with tranid and trancode with orderid values

I tried running the below statement

Update sales set date = o.orddate from order as o inner join sales as s on 
o.tranid = s.tranid and o.orderid = s.trancode 

The table is getting updated but both the date values in sales table is getting updates 2022-06-01 it's not getting updated with respect to tranid and trancode. Any better way to code this?

CodePudding user response：

You dont need to rejoin with order. Its a simple update.

update sales s set date1=o.orddate
from orders o where s.tranid = o.tranid and s.trancode = o.orderid;

Table after update -

select * from sales;