Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Complex Update Query

Posted on 2004-10-30
8
Medium Priority
?
227 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:rikroket
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 9

Expert Comment

by:paelo
ID: 12454356
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
 
LVL 8

Expert Comment

by:SashP
ID: 12454521
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
 
LVL 8

Expert Comment

by:SashP
ID: 12454524
Replace the @a variable with the name of your table
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Accepted Solution

by:
SashP earned 2000 total points
ID: 12456419
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
 
LVL 2

Expert Comment

by:Kolya_Tchernitsky
ID: 12456817
Or, even faster:

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

Expert Comment

by:SashP
ID: 12458150
Koyla

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

Expert Comment

by:Kolya_Tchernitsky
ID: 12458347
Oops,
You're right.
0
 

Author Comment

by:rikroket
ID: 12468713
Thanks to all who replied with solutions and thanks to SashP; I implemented your solution and it worked perfectly.

Rik
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question