We help IT Professionals succeed at work.

Related to SQL Query

Satish B
Satish B asked
on
119 Views
Last Modified: 2017-04-19
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

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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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);

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions