Update field based on next 5 rows

  • Hi,

    I have table below:

    ID   Postcode   PostcodeNext

    1       LL11

    2       LL12

    3       LL13

    4       LL14

    5        LL15

    6       LL16

    .

    .

    .

    I need to update PostcodeNext column for each row in database with next 5 postcodes as string, so ie value ID 1 would be:

    ID   Postcode   PostcodeNext

    1       LL11          LL12,LL13,LL14,LL15,LL16

    • This topic was modified 7 months, 1 week ago by  pahulyi.
  • Did you try this ?

    Select *
    , stuff(
    (
    select rtrim(T2.postcd) + ', '
    from #tmp T2
    where T2.idnr between T1.idnr + 1 and T1.idnr + 5
    order by T2.idnr
    for xml path('P'), root('R'), type
    ).query('R/P').value('.', 'varchar(64)'), 1, 0, '')
    from #tmp T1

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi Johan,

    How do i get #tmp T1 and #tmp T2? and also once i got string, how do i update that PostcodeNext field for all rows?

    My table looks like this

    CREATE TABLE [dbo].[postcode](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [Postcode] [varchar](50) NULL,

    [PostcodeNext] [varchar](100) NULL,

    CONSTRAINT [PK_postcode_1] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    • This reply was modified 7 months, 1 week ago by  pahulyi.
  • PostCodeNext is not long enough to hold 5 PostCodes, commas and spaces.

     

    • This reply was modified 7 months, 1 week ago by  homebrew01.
    • This reply was modified 7 months, 1 week ago by  homebrew01.
    • This reply was modified 7 months, 1 week ago by  homebrew01.
    • This reply was modified 7 months, 1 week ago by  homebrew01.
  • LL12,LL13,LL14,LL15,LL16 has only 25 characters, column can hold up to 100, even if it was short, i could increase it, i just need to know how to update that column with next 5 postcodes

  • PostCode is varchar(50). 50 x 5 = 250 plus commas ( and spaces ?).

    #TMP is your table name. Johan did not know the name of your table at the time.

    Using Johan's code, perhaps you can use it to build an update statement, based on the results it gives.

    • This reply was modified 7 months, 1 week ago by  homebrew01.
    • This reply was modified 7 months, 1 week ago by  homebrew01.
  • Postcode is 50, but actual string never will be more that 4 digits/letters

  • Never say "Never" !  Why is postcode varchar(50) ?

    Also, Doesn't seem like a good idea to have the table name the same as a column name.

    • This reply was modified 7 months, 1 week ago by  homebrew01.
  • its just how it was designed, but this is not a problem, that string in Postcode column will not be bigger than 4 characters, i just need script to update, length is irrelevant here

  • Johan's code assumes postcode is entered sequentially and uses ID  to order them.

    But if LL15 was Inserted before LL14 then you will get different results than you want because the ID for LL15 will be lower then LL14's ID.

    In that case, you could copy the records into a temp table with an identity field, ordered by postcode, so they are both in sequence.

    Then use the temp table to update the live table.

    • This reply was modified 7 months, 1 week ago by  homebrew01.
  • Hi Johan, script worked well, but how can i remove last comma (,), ideally i dont want last one showing

  • Select *

    , stuff( ( select rtrim(T2.Postcode) + CASE WHEN T2.id = T1.id + 5 then '' else ', ' end

    from Postcodetbl T2

    where T2.id between T1.id + 1 and T1.id + 5

    order by T2.id

    for xml path('P'), root('R'), type

    ).query('R/P').value('.', 'varchar(64)'), 1, 0, '')

    from postcodetbl T1

    • This reply was modified 7 months, 1 week ago by  homebrew01.
  • Since you're on SQL Server 2019, you shouldn't need to use XML and STUFF etc. any more - you can use the STRING_AGG function, which became available with SQL Server 2017. That will make it a little more straightforward, I think.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

    However, I don't have access to that version, so I can't provide a direct answer as to how the actual query would be like for your use case.

  • Please select Johan's post as the correct answer (not mine). He did all the work.

    • This reply was modified 7 months, 1 week ago by  homebrew01.

Viewing 14 posts - 1 through 13 (of 13 total)

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