June 20, 2014 at 4:13 am
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? (
June 20, 2014 at 6:03 am
This all looks so confusing..
You are trying to update column P of table_1 with column P of another copy of table_1 matched on table_1.link = selrow.link where selrow again is table_1
Maybe this...
UPDATE t1
SET P = T.P1
FROM table_1 t1
CROSS APPLY
(SELECT TOP 1 isNULL(P, 0) + 1 P1 FROM table_1 t2 WHERE t1.link=t2.link ORDER BY.....)T
EDIT:Make sure you have a relevant ORDER BY clause with the TOP query.Sometime back I had to pay heavily for missing that part in one of our production servers.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 20, 2014 at 6:21 am
thanks my friend
June 20, 2014 at 7:30 am
Or:
UPDATE t1 SET
P = T.NewP
FROM table_1 t1
CROSS APPLY (
SELECT NewP = ISNULL(MAX(p),0)+1
FROM table_1 t2
WHERE t1.link = t2.link
) t
What can you tell us about the distribution of values in column [Link]?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply