update error - 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

update error

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-06-20 : 06:11:43

did so

UPDATE table_1
SET P = ( select isNULL(P, (select TOP 1 isNULL(P, 0) + 1
from table_1
order by 1 desc))
from table_1
where link = selrow.link
)
FROM (select LINK from table_1) SELROW
WHERE table_1.link = selrow.link

Only here still not working as it should.

P LINK
--------------------------------

| NULL | 100 |
| NULL | 200 |
| NULL | 300 |
| 11 | 400 |
| NULL | 500 |
--------------------------------

At the output should look like:

P LINK
--------------------------------

| 12 | 100 |
| 13 | 200 |
| 14 | 300 |
| 11 | 400 |
| 15 | 500 |
--------------------------------

I can not understand why not produce? (

http://sql-az.tr.gg/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-06-20 : 07:18:26

[code]
-- *** Test Data ***
CREATE TABLE #table1
(
P int NULL
,Link int NOT NULL
);
INSERT INTO #table1
SELECT NULL, 100
UNION ALL SELECT NULL, 200
UNION ALL SELECT NULL, 300
UNION ALL SELECT 11, 400
UNION ALL SELECT NULL, 500;
-- *** End Test Data ***

select * from #table1;

WITH Base AS (SELECT MAX(COALESCE(P,0)) AS MP FROM #table1)
,OffSet AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Link) AS rn FROM #table1 WHERE P IS NULL)
UPDATE S
SET P = B.MP + S.rn
FROM OffSet S, Base B;

select * from #table1;
[/code]

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-06-20 : 07:36:46

thanks
my friend

http://sql-az.tr.gg/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources