Satish B
asked on
Related to SQL Query
I have 2 tables e.g. a and b having account_no as common field i want to update table b if same account_no exist in table a then i need to update the status column in b table as Not paid else Paid also want yesterdays date in table b under Payment_date column if status is Paid
ASKER
When matching account in both table then i need as Not_Paid other wise Paid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much Mr.Vitor for solution provided its giving me exact result.
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) ;
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)
- 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