Rephase my question on SQL-UPDATE

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

SQL Server Development (2000)

Rephase my question on SQL-UPDATE

Author

Topic

joriveek
Starting Member

33 Posts

Posted - 2006-04-11 : 04:07:43

Hi,

Let me rephase my question on SQL Update:

I want to perform Update on TOP(1) only result set has more than one row yielded after matching search criteria(in WHERE cluase).

I want to perform Update on All the rows in a result set in case of distinct rows.

At the moment, if I use TOP(1), Update performs on the Top(1) all the times, irrespective of the result set is unique or not.

I want two are done in a single UPDATE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 04:13:16

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

joriveek
Starting Member

33 Posts

Posted - 2006-04-11 : 04:36:12

Case 1:
Description Value Flag
--------------------------

Item_1 300
Item_1 300

I need to set the Flag to 0 for TOP(1) row only, because data is same here and ignore the second record

Case 2)
Description Value Flag
--------------------------

Item_1 300
Item_1 600

In this case I need to set the Flag to 0 for both rows,

I want one single Update Statement to cater the both of the above cases

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 04:58:12

Thats why you need to have unique column to avoid duplicates
Why do you want to do this?
Give more details on what you are trying to do

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-04-11 : 04:59:06

You could try try something like this:


SELECT Description, Value
FROM TableName
GROUP BY Description, Value

IF @@ROWCOUNT > 1
BEGIN
	SET ROWCOUNT 1

	-- Do Update

	SET ROWCOUNT 0
END
ELSE
BEGIN
	-- Do Update
END

Depending on your exact situation you might want to select a count into a local variable and test that rather than @@ROWCOUNT.

-Lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources