Related to SQL Query

Satish B
Satish B used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Mike EghtebasDatabase and Application Developer

Commented:
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
Satish BSr. MIS executive Database Management

Author

Commented:
When matching account in both table then i need as Not_Paid other wise Paid
IT Engineer
Distinguished Expert 2017
Commented:
If I understood correctly your requirement then you'll need two updates. One for NOT PAID (matching records with table A) and second one for PAID (not matching records):
UPDATE b
SET b.status = 'Not paid'
FROM TableB b
	INNER JOIN TableA a ON b.account_no = a.account_no

UPDATE b
SET b.status = 'Paid',
	b.Payment_date = GETDATE()-1
FROM TableB b
WHERE NOT EXISTS (SELECT 1
	FROM TableA a 
	WHERE b.account_no = a.account_no)

Open in new window

Satish BSr. MIS executive Database Management

Author

Commented:
Thank you so much Mr.Vitor for solution provided its giving me exact result.
Steven VellaIT Adviser

Commented:
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);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial