update error

Question

update error

gurbanov.1984

Mr or Mrs. 500

Points: 508
More actions
June 20, 2014 at 4:13 am

#309318

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? (

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

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

gurbanov.1984 Mr or Mrs. 500 Points: 508 More actions · Answer 2

gurbanov.1984

Mr or Mrs. 500

Points: 508

June 20, 2014 at 6:21 am

#1723560

thanks my friend

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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]?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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