Solved

Updating record reference number using the previous record's reference number

Posted on 2009-03-31
7
897 Views
Last Modified: 2012-06-27
I have the table below in SQL Server 2005:

id  reference  record_id
1   1234-4413   1
2   1234-0d33   1
3   NULL        1
4   1234-0d18   1
5   aaa         1
6   1234-3913   1
7   1234-4223   1

If there is not a one in the record_set field, then that field need's the previous record's reference number.

i.e. record 5 does not have a 1 set, therefore it needs the reference number from 4 and record_id needs to be set to two. Giving me the following, after an update:
id  reference  record_id
1   1234-4413   1
2   1234-0d33   1
3   1234-0d33   2
4   1234-0d18   1
5   1234-0d18   2
6   1234-3913   1
7   1234-4223   1

I have to update large numbers of records [400'000+], so cursors are not an option.

CREATE TABLE [dbo].[products](

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

	[reference_number] [varchar](20) NULL,

	[record_set] [varchar](2) NULL,

CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED

Open in new window

0
Comment
Question by:egxis
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
have a look
update products set

reference_number=(select pc.reference_number from products pc where pc.id=products.id-1) 

where products.record_set<>1

Open in new window

0
 

Author Comment

by:egxis
Comment Utility
I tried your code and got an error?
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "pc.record_set" could not be bound.

I think my snippet below reflects my query above.



declare @t table ( id varchar(2), reference_number varchar(20), record_id varchar(2))

insert into @t values ( '1', '1234-4413', '1' )

insert into @t values ( '2', '1234-0d33', '1' )

insert into @t values ( '3', '', '' )

insert into @t values ( '4', '1234-0d18', '1' )

insert into @t values ( '5', 'aaa', '' )

insert into @t values ( '6', '1234-3913', '1' )

insert into @t values ( '7', '1234-4223', '1' )
 

update @t set

reference_number=(select pc.reference_number from @t pc where pc.id=pc.id-1) 

where pc.record_set<>1
 

select * from @t

Open in new window

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
Comment Utility
it is creating problem with table variable but you can use below script, i created physical table and used the script.



--create table

CREATE TABLE [dbo].[products](

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

        [reference_number] [varchar](20) NULL,

        [record_set] [varchar](2) NULL

)

--insert records

insert into products

SELECT    '1234-4413', '1' UNION ALL

SELECT   '1234-0d33' , '1' UNION ALL

SELECT   NULL         ,'' UNION ALL

SELECT    '1234-0d18'  ,'1' UNION ALL

SELECT    'aaa'        ,'2' UNION ALL

SELECT    '1234-3913'  ,'1' UNION ALL

SELECT    '1234-4223'  ,'2'
 

--update

update products set

reference_number=(select pc.reference_number from products pc where pc.id=products.id-1) 

where products.record_set<>1
 

--select records

select * from products

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Is the data such that you are CERTAIN there are no gaps in the numbers?  That makes it sooo much easier of course :-) .
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Oh, and, I assume there could be more than one row in sequence with a missing reference.  For example, that this is possible:

1   1234-4413   1
2   1234-0d33   1
3   NULL        1
4   NULL        1
5   aaa          1
6   1234-3913   1

Or is it ALWAYS only ONE missing row in sequence?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Ok, assuming the ids ARE sequential, I think this UPDATE will do it:

UPDATE p
SET reference_number = baseP.reference_number,
    record_set = CAST(baseP.record_set AS int) + 1
FROM products p
INNER JOIN products baseP ON baseP.id = p.id - 1 AND
    baseP.reference_number LIKE '1%'
WHERE p.reference_number IS NULL OR p.reference_number NOT LIKE '1%'


If there could be more than 1 row in a row, then add a loop to execute the code until no "missing" reference_numbers are found:

DECLARE @forceRowcountTo1 TINYINT
SET @forceRowcountTo1 = 1

WHILE @@ROWCOUNT > 0
UPDATE p
SET reference_number = baseP.reference_number,
    record_set = CAST(baseP.record_set AS int) + 1
FROM products p
INNER JOIN products baseP ON baseP.id = p.id - 1 AND
    baseP.reference_number LIKE '1%'
WHERE p.reference_number IS NULL OR p.reference_number NOT LIKE '1%'
0
 

Author Comment

by:egxis
Comment Utility
@RiteshShah:
Thanks for your prompt responses, I will test tomorrow.

@ScottPletcher:
I currently have a function which performs something similar to the code you provided, but it's very sloooow. This is not a slight on your code [as I have not analysed it], but I need something lean and mean.

Which method might give me better results on if it were run on 400k+ rows?

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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

6 Experts available now in Live!

Get 1:1 Help Now