CODEWITHSUNDEEP
sqlsql-server
geek
geek

Reputation: 65

Update a table based on a condition

I have a column Table1.Tradedate and another column Table2.SettlementDate. Based on the comparison between these 2, I want to update a column in table 2

IF (Table1.TradeDate <= Table2.SettlementDate)
BEGIN
UPDATE Table2 SET Status='Y'
END

This is what I have tried but I know its wrong, since the table will obviously contain more than 1 records. So, I believe what I should do is

  1. use a join on 2 tables based on some @id to pick a particular record
  2. check the IF condition for that particular record
  3. update the Status column in table2.

I hope my approach is correct but I am writing it incorrectly.

Table1:

SKacc | Name | TradeDate   | Othercolumns....
1     | xxx  |  01/07/2019 |
2     | xxx  |  01/06/2019 |

Table2: 

SKAcc | Name | SettlementDate | Status |Other Columns....
1     | xxx  |  01/08/2019    | NULL   |
2     | xxx  |  01/08/2019    | NULL   |

Upvotes: 1

Views: 106

Answers (3)

nbirla
nbirla

Reputation: 610

Try joining the two tables with the related column and then update the table you want to update with the value. Using inner join in the example but can change depending on the usecase

UPDATE Table2 
  SET Status = 'Y'
  FROM Table2
  INNER JOIN Table1 ON Table1.id = Table2.table1_id
  WHERE Table1.TradeDate <= Table2.SettlementDate

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would not recommend a JOIN for this purpose. Instead:

update table2
    set Status = 'Y'
    where exists (select 1
                  from table1 t1
                  where t1.id = t2.id and
                        t1.tradeDate <= t2.settlementDate
                 );

The reason I recommend this version is because you have not specified that id is unique in table1. In general, you only want to use JOIN in UPDATE when you can guarantee that there is only one matching row.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try below

update t2 set Status = 'Y'
from table2 t2
join table1 t1 on t1.id = t2.id
where t1.tradeDate <= t2.settlementDate

Upvotes: 3

Related Questions