Solved

Complex Update Query

Posted on 2004-10-30
222 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
Question by:rikroket
    8 Comments
     
    LVL 9

    Expert Comment

    by: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.
    0
     
    LVL 8

    Expert Comment

    by: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
    0
     
    LVL 8

    Expert Comment

    by:SashP
    Replace the @a variable with the name of your table
    0
     
    LVL 8

    Accepted Solution

    by:
    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
    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
    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
    Oops,
    You're right.
    0
     

    Author Comment

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

    Rik
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    SQL Server Side Trace is a technique of Profiling SQL Server Events Silently (i.e without Using the Profiling Tool). Running a visual tool in production increases overhead, but we can develop server side Trace using Sql Server Profiler itself. We…
    I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now