UPDATE using Having clause - 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 2000 Forums

Transact-SQL (2000)

UPDATE using Having clause

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2004-03-18 : 08:21:18

Is it possible to use UPDATE together with the Having clause?

I would like to do something like this:

UPDATE OrderDelivery
SET ConfirmedDeliveryDate = '2004-04-18'
GROUP BY LineNumber, PurchOrderNo
HAVING COUNT(LineNumber) = 1
AND PurchOrderNo = '0000041281'

Data looks like this, I just want to retreiv the rows which has just one LineNumber.

PurchOrderNo LineNumber
0000041281 00001
0000041281 00001
0000041281 00002
0000041281 00003

/Magnus

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-18 : 09:06:23

I think you will need to use a rather convulted self-join, with a Derived table(!) here:


UPDATE O
SET ConfirmedDeliveryDate = '2004-04-18'
FROM OrderDelivery O
INNER JOIN 
(
 SELECT LineNumber 
 FROM OrderDelivery 
 WHER PurchOrderNo = @PurchOrderNo
 GROUP BY LineNumber
 HAVING COUNT(*) = 1
) X
ON O.LineNumber = X.LineNumber
WHERE O.PurchOrderNo = @PurchOrderNo

Rauken
Posting Yak Master

108 Posts

Posted - 2004-03-18 : 09:14:05

Thanks!! It works perfect. I've learned something new today :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources