Related to SQL Query

Please verify the following 3 items are what you need:

- update table b if same account_no exist in table a

- update the status column in b table as Not paid else Paid
Question: it is not clear when Not paid and when Paid.  Provide criteria

- set b.Payment_date to yesterdays date.   <-- verify

Had to make a few assumptions, but here is a draft for your consideration.

Note: Consider using select statements to test the statement before changing live data i.e. SELECT b.status, getdate()-1 FROM b WHERE EXISTS (...).

update
  b
set
  b.status='Paid',
  b.payment_date=GETDATE()-1
where
  b.status is null and
  exists (select a.account_no
    from a
    where a.account_no=b.account_no);

update
  b
set
  b.status='Not Paid'
where
  b.status is null and
  not exists (select a.account_no
    from a
    where a.account_no=b.account_no);