Where In Having Count - 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)

Where In Having Count

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-27 : 15:14:55

This is not quite working the way it should:

	UPDATE ImportNumbers SET StatusMsg = 'Duplicate Numbers Found'
	WHERE InvoiceNum IN (
		SELECT InvoiceNum
		FROM ImportNumbers
		GROUP BY InvoiceNum
		HAVING Count(*) > 1 
		)
		AND LocationID = @LocationID

If there is another InvoiceNum in the table but a different LocationID, it updates and it shouldn't.

Suggestions?

Thanks,

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-27 : 15:25:11

Ok, got it working.
Should have had an additional WHERE

SELECT invoicenum FROM ImportNumbers
WHERE LocationID = @LocationID
GROUP BY invoicenum HAVING COUNT(*) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources