UPDATE question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-09-15 : 09:49:29

I have two tables:


Client
-----

clientID  FirstPaymentDate
1		null
2		null
3		null
4		null
5		null



Payments
--------

clientID  PaymentDate
2  	     02/07/2008
1	     03/07/2008
2	     07/08/2008

How can I update the Client table so that

the FirstPaymentDate is the oldest PaymentDate for that ClientID if the FirstPaymentDate field is null in the Client table please?

So I would get the result:


Client
-----

clientID  FirstPaymentDate
1		03/07/2008
2		02/07/2009
3		null
4		null
5		null

*UK format dates

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-15 : 09:55:30

Update c
set c.FirstPaymentDate=p.PaymentDate
from Client as c inner join
(
select clientID, min(PaymentDate) as PaymentDate from payments group by Clientid
) as p
on c.Clientid=p.Clientid

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-15 : 10:11:20

[code]DECLARE @Client TABLE(clientID INT, FirstPaymentDate DATETIME)
INSERT INTO @Client SELECT
1, null UNION SELECT
2, null UNION SELECT
3, null UNION SELECT
4, null UNION SELECT
5, null

DECLARE @Payments TABLE(clientID INT, PaymentDate DATETIME)
INSERT INTO @Payments SELECT
2, '02/07/2008' UNION SELECT
1, '03/07/2008' UNION SELECT
2, '07/08/2008'

SELECT c.clientID, p.PaymentDate
FROM @Client c LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY clientID ORDER BY PaymentDate DESC)x, PaymentDate, clientID FROM @Payments)p on c.clientID=p.clientID
WHERE x=1 OR p.PaymentDate IS NULL[/code]
not sure correct...but hope can help you

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-15 : 10:12:12

OMG

Hope can help...but advise to wait pros with confirmation...

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-09-15 : 10:21:49

Many thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources