If statement + select - 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

General SQL Server Forums

New to SQL Server Programming

If statement + select

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-22 : 11:52:36

Hi,

I have a table with the following sample data:
Number Name
1, Sales
2, Expenses
NULL Sales
NULL Netprofit

I would like to create an if statement that will look up the table values that have NULL as a Number and assign Numbers based on 2 criteria:
If Name = the name of any of the table Names with assigned Number, then the Number that is Null will equal the number of the same name table value (ex: the third row's Number will be 1 as Sales = Sales)
If Name is not the same with any of the other names in the table that have assigned numbers, then the Number of this Name will equal the MAX of all Column Numbers + 1 (ex: netprofit name will have Number 3)

I started the if statement like shown below but it gives me the error : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If (select Name from SampleTable where Number is null)= (select Name from SampleData where Number is not null)
Begin ...

Any ideas on how I can query the table one row at a time and input the result, and then continue with the next row. This table will potentially have many rows and new values will get added.

Thanks!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-12-22 : 12:48:24

With SQL2005 and above, try something like:


UPDATE T
SET Number =
	CASE
		WHEN D1.Number IS NULL
		THEN D2.Number + D1.RowNum
		ELSE D1.Number
	END
FROM SampleTable T
	JOIN
	(
		SELECT T1.Name, D.Number
			,DENSE_RANK() OVER (PARTITION BY D.Number ORDER BY T1.Name) AS RowNum
		FROM SampleTable T1 WITH (UPDLOCK)
			LEFT JOIN
			(
				SELECT T2.Name, T2.Number
				FROM SampleTable T2 WITH (UPDLOCK)
					JOIN SampleTable T3 WITH (UPDLOCK)
						ON T2.Name = T3.Name
				WHERE T2.Number IS NOT NULL
					AND T3.Number IS NULL
			) D
				ON T1.Name = D.Name
		WHERE T1.Number IS NULL
	) D1
		ON T.Name = D1.Name	
	CROSS JOIN
	(
		SELECT MAX(COALESCE(T4.Number, 0)) AS Number
		FROM SampleTable T4 WITH (UPDLOCK, SERIALIZABLE)
	) D2
WHERE T.Number IS NULL

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-22 : 12:59:13

Thanks!
hmm, isn't there a simpler way to solve this? i do not understand most of the statements in the above solution..
Yes - I am using sql server 2005.
Please advise

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-22 : 16:04:28

I think the error I am getting is because the select statement I wrote above returns more than 1 row at a time and that is why the IF condition cannot be executed. What statement or approach should I use to select ONLY 1 NULL NUmber ROW at a time from the table and compare the NAME value with the Names in the rows that have actual values for their Number. if there is a match, one value is returned for the number of this row, if not, another number is returned.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-12-23 : 05:39:39

1. The structure of your table suggests you have problems with your schema. You should really have a master table with unique combinations of Number and Name and a sub-table with multiple numbers. I appreciate that you sometimes have to live with what some third party application has given you.

2. The whole point of SQL is to do things in a set based manner. You will have greater problems with transactions and concurrency if you attempt to do this in a procedureal manner.
I have tidied up the code below by using CTEs instead of derived tables.
I suggest that you run the commented out selects to see what each part of the query produces.
Anything you do not understand can be looked up in BOL.


;WITH NameNumbers
AS
(
	SELECT DISTINCT Name, Number
	FROM SampleTable
	WHERE Number IS NOT NULL
)
,NullNumbers
AS
(
	SELECT DISTINCT T.Name, N.Number
		,DENSE_RANK() OVER (PARTITION BY N.Number ORDER BY T.Name) AS OffSet
	-- UPDLOCK indicates that intend to update these rows
	FROM SampleTable T WITH (UPDLOCK)
		LEFT JOIN NameNumbers N
			ON T.Name = N.Name
	WHERE T.Number IS NULL
)
,MaxNumber
AS
(
	-- SERIALIZABLE applies a key range lock.
	-- UPDLOCK helps in some circumstances
	SELECT MAX(COALESCE(Number, 0)) AS MaxNumber
	FROM SampleTable WITH (UPDLOCK, SERIALIZABLE)
)
-- select * from NameNumbers
-- select * from NullNumbers
-- select * from MaxNumber
UPDATE T
SET Number =
	CASE
		WHEN N.Number IS NULL
		THEN M.MaxNumber + N.OffSet
		ELSE N.Number
	END
-- select *
FROM SampleTable T
	JOIN NullNumbers N
		ON T.Name = N.Name
	CROSS JOIN MaxNumber M
WHERE T.Number IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources