Solved

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

Posted on 2009-03-31
7
902 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
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24028478
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
ID: 24028548
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
ID: 24028818
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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 24031534
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:Scott Pletcher
ID: 24031610
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:Scott Pletcher
ID: 24031979
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
ID: 24033074
@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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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