asked on

Complex Update Query

I need to write an update query that will do the following:

Turn this table:
ID PersonNo Notes
-- ----------- ------
1 <NULL> Record: 1
2 <NULL> <NULL>
3 <NULL> Note 1
4 <NULL> Note 2
5 <NULL> Record: 2
6 <NULL> <NULL>
7 <NULL> Note 1
8 <NULL> Record: 3
9 <NULL> <NULL>
10 <NULL> Note 1
...

Into this table:
ID PersonNo Notes
-- ----------- ------
1 1 Record: 1
2 1 <NULL>
3 1 Note 1
4 1 Note 2
5 2 Record: 2
6 2 <NULL>
7 2 Note 1
8 3 Record: 3
9 3 <NULL>
10 3 Note 1
11 3 Note 2
12 3 Note 3
...
Thanks in advance for your help.

Rik

paelo

Although I don't know the whole situation, I don't see why you need to update and double-store data when you could create a view with PersonNo as a calculated field, like so:

CREATE VIEW dbo.yourview
AS
SELECT t1.ID,
CASE
WHEN t1.Notes LIKE '%Record%'
THEN CONVERT(int,RIGHT(t1.Notes,1))
ELSE (
SELECT TOP 1 CONVERT(int,RIGHT(t2.Notes,1))
FROM dbo.yourtable t2
WHERE t2.ID<t1.ID AND t2.Notes LIKE '%Record%'
ORDER BY t2.ID DESC)
END AS [PersonNo], t1.Notes
FROM dbo.yourtable t1

-Paul.

SashP

Hi rikroket

declare @a table (Id int, PersonNo int, Notes varchar(50))

insert into @a values ( 1 , NULL, 'Record: 1')
insert into @a values ( 2 , NULL , NULL )
insert into @a values ( 3 , NULL , 'Note 1' )
insert into @a values ( 4 , NULL , 'Note 2' )
insert into @a values ( 5 , NULL , 'Record: 2' )
insert into @a values ( 6 , NULL , NULL )
insert into @a values ( 7 , NULL , 'Note 1' )
insert into @a values ( 8 , NULL , 'Record: 3' )
insert into @a values ( 9 , NULL , NULL )
insert into @a values ( 10 , NULL , 'Note 1' )

update @a
set PersonNo =
cast (Replace((select Notes From @a a2 where a2.Id =
(select max(id) from @a a1 where a1.Id <= a.Id and Notes like 'Record:%')),'Record:','') as int)
from @a a

select * from @a

Cheers Sash

SashP

Replace the @a variable with the name of your table

ASKER CERTIFIED SOLUTION

SashP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kolya_Tchernitsky

Or, even faster:

update #a set PersonNo = (select count(*) from #a c where c.ID<=#a.id and Notes like 'Record:%')

SashP

Koyla

Your Logic only works while all record numbers exist in the table, if a record gets deleted it fails.