Updation of a Table with Nearest Greatest Value

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)

Updation of a Table with Nearest Greatest Value

Author

Topic

Sudha1234567
Starting Member

5 Posts

Posted - 2009-08-31 : 10:49:14

i has a table named temp with fields

The Temp Table is as Below
ID NAME
1 System1
2 System2
3 System3
4 NULL
5 NULL
6 System5
7 System6

The ID 4 Name has to be Updated wih the Next greatest Value which has Name NOT NULL

I has to update the name with System4 (by taking next maximum value of ID to the ID which name NULL)
i.e (The ID 4 has name NULL and it has to take the next greatest value which has name Not NULL i.e ID 6
Si it has to update the name as System5 for ID 4)

can any one help me how can we approach this

cat_jesus
Aged Yak Warrior

547 Posts

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

[code]
CREATE TABLE #temp
(id INT,
NAME VARCHAR(15)
)
INSERT INTO #temp

select '1', 'System1'
union all select '2', 'System2'
union all select '3', 'System3'
union all select '4', NULL
union all select'5', NULL
union all select '6', 'System5'
union all select '7', 'System6'

SELECT * FROM #temp WHERE NAME IS NOT NULL

SELECT id, COALESCE(NAME,(SELECT TOP 1 NAME FROM #Temp WHERE id > A.ID AND NAME IS NOT NULL ORDER BY ID ))
FROM #temp A

[/code]

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-31 : 11:31:28

Oh you wanted an update.

Here's the same thing as an update.


UPDATE A SET A.NAME = (SELECT TOP 1 NAME FROM #Temp WHERE id > A.ID  AND NAME IS NOT NULL ORDER BY ID )
FROM #temp A
WHERE A.NAME IS NULL

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources