Need help with an Update Subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with an Update Subquery

Author

Topic

roguebuckeye
Starting Member

1 Post

Posted - 2010-08-24 : 22:40:26

I need help with an update subquery that will pull data from my customers table and my billing table in order to update a column within the customer table. I will just show you what I have so far, and if you can help i would appreciate it greatly.

CUSTOMER TABLE DETAILS:
CUSTID
CUSTSTATUS
CUSTDELETERZN
CUSTDELETEDATE
EMPID
CUSTFIRSTNAME
CUSTLASTNAME
CUSTSTARTDATE
PACKID
CUSTPHONE
CUSTSTREET
CUSTCITY
CUSTSTATE
CUSTZIP
CUSTEMAIL

BILLING TABLE DETAILS:
CUSTID
BILLID
BILLAMOUNT
DUEDATE
PAIDAMOUNT
PAIDDATE

HERE IS THE CODE I HAVE SO FAR: (please do not laugh, I am still learning)
UPDATE customer
SET custstatus = 'D'
WHERE
(SELECT bi.duedate, bi.custid, bi.paiddate, cu.custid
FROM billing bi, customer cu
WHERE sysdate - bi.duedate > 14
AND cu.custid = bi.custid);

I need the code to check if the customer is 14 days past due on their account, and if so update the custstatus with a 'D' (Deactivated) as opposed to the 'A'(Active) that is in the record now.

So for the long post, but I could really use some help.

Thanks,
Rogue

maevr
Posting Yak Master

169 Posts

Posted - 2010-08-25 : 03:52:16

Use a join to update the column.

update a set a.custstatus = 'D'
from CUSTOMER a right outer join BILLING b
on a.custid = b.custid
and DATEDIFF(day, duedate, GETDATE()) > 14

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:38:36

[code]
UPDATE c
SET c.custstatus='D'
FROM CUSTOMER C
INNER JOIN BILLING b
on a.custid = b.custid
WHERE b.duedate < DATEADD(dd,-14,GETDATE())
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources