Update a Table - 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)

Update a Table

Author

Topic

sudha12345
Starting Member

47 Posts

Posted - 2009-09-07 : 06:11:14

i has a table named temp with fields

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

The ID 6 Name has to be Updated wih the Next Max least Value which has Name NOT NULL

I has to update the name with System6 (by taking next maximum Least value of ID to the ID which name NULL)
i.e (The ID 6 has name NULL and it has to take the next max Leasr value which has name Not NULL i.e ID 5
Si it has to update the name as System6 for ID 6)

I wrote a query as below

UPDATE A SET A.NAME = (SELECT NAME FROM table WHERE id < A.ID AND NAME IS NOT NULL ORDER BY ID )
FROM table A
WHERE A.NAME IS NULL

it is taking the name of ID 1 and updating

But it has to take the ID 5 and has to update as it is nearest to 6

can any one modify the query

Sudhakar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 07:22:10

Don't order by - just use max(Name)

No, you're never too old to Yak'n'Roll if you're too young to die.

sudha12345
Starting Member

47 Posts

Posted - 2009-09-07 : 07:31:01

still not working

Sudhakar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-07 : 09:04:47

UPDATE A SET A.NAME = (SELECT max(NAME) FROM A WHERE id < A.ID AND NAME IS NOT NULL)
FROM A
WHERE A.NAME IS NULL

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-07 : 09:14:12

So my answer was too short?