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
rikroketAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SashPConnect With a Mentor Commented:
update [Table Name]
    set PersonNo =
    cast (Replace((select Notes From [Table Name] a2 where a2.Id =
        (select max(id) from [Table Name] a1 where a1.Id <= a.Id and Notes like 'Record:%')),'Record:','') as int)
from [Table Name] a
0
 
paeloCommented:
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.
0
 
SashPCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SashPCommented:
Replace the @a variable with the name of your table
0
 
Kolya_TchernitskyCommented:
Or, even faster:

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

Your Logic only works while all record numbers exist in the table, if a record gets deleted it fails.  
0
 
Kolya_TchernitskyCommented:
Oops,
You're right.
0
 
rikroketAuthor Commented:
Thanks to all who replied with solutions and thanks to SashP; I implemented your solution and it worked perfectly.

Rik
0
All Courses

From novice to tech pro — start learning today.