June 16, 2008 at 6:23 am
Hi I have folowing table
Name Code Last_code
MC1 100
MC2 200
MC3 250
MC4 300
MC5 320
MC6 360
I need to update 'Last_code' with the next Value from collumn 'Code' - 1.
Like this:
Name Code Last_code
MC1 100 199
MC2 200 249
MC3 250 299
MC4 300 319
MC5 320 359
MC6 360
How do I do this in SQL?
Not sure
thanks 🙂
June 16, 2008 at 6:55 am
There must be a more efficient way of doing this, but APPLY would handle it pretty easily.
Jeff may have a way of doing this without hidden RBAR.
[font="Courier New"]DROP TABLE #Sample
GO
CREATE TABLE #Sample ([Name] CHAR(3), Code INT, Last_Code INT)
GO
INSERT #Sample ([Name], Code, Last_Code)
SELECT 'MC1',100,NULL
UNION SELECT 'MC2',200,NULL
UNION SELECT 'MC3',250,NULL
UNION SELECT 'MC4',300,NULL
UNION SELECT 'MC5',320,NULL
UNION SELECT 'MC6',360,NULL
GO
UPDATE
Cur
SET
Cur.Last_Code = Next.Code-1
FROM
#Sample Cur
OUTER APPLY (SELECT TOP 1 X.* FROM #Sample X
WHERE X.[Name] > Cur.Name
ORDER BY X.[Name] ASC) Next
GO
SELECT
*
FROM
#Sample[/font]
June 16, 2008 at 7:07 am
Gotta run now, so without explaining... I'll be back later, sorry
/*objects and test data*/
create table table1(name varchar(10), code int, last_code int)
insert into table1 values ('MC1',100,NULL)
insert into table1 values ('MC2',200,NULL)
insert into table1 values ('MC3',250,NULL)
insert into table1 values ('MC4',300,NULL)
insert into table1 values ('MC5',320,NULL)
insert into table1 values ('MC6',360,NULL)
insert into table1 values ('MC10',500,NULL)
/*select to check whether join is correct*/
select *
from table1 a
join table1 b on b.name = LEFT(a.name,2) + CAST(CAST(SUBSTRING(a.name,3, LEN(a.name)) as INT) + 1 as varchar(10))
/*update last_code = (code from the next row - 1)*/
UPDATE a
SET last_code = b.code - 1
FROM table1 a
JOIN table1 b ON b.name = LEFT(a.name,2) + CAST(CAST(SUBSTRING(a.name,3, LEN(a.name)) as INT) + 1 as varchar(10))
/*view result: a gap in sequence will cause problems!*/
select *
from table1 a
/*cleanup*/
DROP TABLE table1
June 16, 2008 at 9:17 am
Thank you guys. I'll try both tomorrow morning 🙂
June 16, 2008 at 9:35 am
Here is another solution:
/*objects and test data*/
create table dbo.table1(name varchar(10), code int, last_code int);
insert into dbo.table1 values ('MC1',100,NULL);
insert into dbo.table1 values ('MC2',200,NULL);
insert into dbo.table1 values ('MC3',250,NULL);
insert into dbo.table1 values ('MC4',300,NULL);
insert into dbo.table1 values ('MC5',320,NULL);
insert into dbo.table1 values ('MC6',360,NULL);
insert into dbo.table1 values ('MC10',500,NULL);
with DataTable (
RowNumber,
name,
code,
last_code
) as (
select
row_number() over (order by code) as RowNumber,
name,
code,
last_code
from
dbo.table1
)
select
*
from
DataTable a
left outer join DataTable b
on (a.RowNumber + 1 = b.RowNumber);
with DataTable (
RowNumber,
name,
code,
last_code
) as (
select
row_number() over (order by code) as RowNumber,
name,
code,
last_code
from
dbo.table1
)
update dbo.table1 set
last_code = b.code - 1
from
dbo.table1 t
inner join DataTable a
on (t.code = a.code)
left outer join DataTable b
on (a.RowNumber + 1 = b.RowNumber)
select * from dbo.table1;
/*cleanup*/
DROP TABLE table1
😎
June 17, 2008 at 3:01 am
Vitali,
the solution depends on what data you can expect in the table.
Are there some rows that begin with other letters (not MC)? If yes, how should these be treated - are they separately numbered sets or should we ignore it and just order everything by Name? Or are we interested in just those with MC?
Can the number exceed 9 (like MC10 - this would affect ordering, and conversion of the numeric part is necessary)?
Can there be some gaps in the numbers, like I posted - MC4, MC5, MC6, MC10? If yes, how to treat them - i.e., in this case, last_code of the row with Name "MC6" should be updated with the value from MC10 or not updated at all?
The posted solutions will work on your tiny (and very probably not representative) sample of data, but might result in absolute nonsense, depending on what your live data look like and what are the exact requirements.
June 17, 2008 at 5:13 am
Good point, my solution should probably order by the Code field in the APPLY, rather than by the Alpha numeric. The other points were why I did not parse the alpha numeric field.
June 18, 2008 at 1:56 am
And this is my solution:
create table dbo.table1(name varchar(10), code int, last_code int);
insert into dbo.table1 values ('MC1',100,NULL);
insert into dbo.table1 values ('MC2',200,NULL);
insert into dbo.table1 values ('MC3',250,NULL);
insert into dbo.table1 values ('MC4',300,NULL);
insert into dbo.table1 values ('MC5',320,NULL);
insert into dbo.table1 values ('MC6',360,NULL);
insert into dbo.table1 values ('MC10',500,NULL);
drop table #tmp1
drop table #tmp2
select row_number() over (order by code) as RowNumber, * into #tmp1 from table1
select row_number() over (order by code) as RowNumber, * into #tmp2 from table1
update table1
set table1.last_code = b.code - 1
from table1
inner join #tmp1 a on table1.code = a.code
left join #tmp2 b on a.Rownumber = b.Rownumber-1
select row_number() over (order by code) as RowNumber, * from table1
June 18, 2008 at 2:11 am
Your solution works, based on what we have from the OP, but why all the extra work setting up and using temporary tables?
😎
June 19, 2008 at 2:12 am
thanks to all of you who helped me out here.
your solutions worked. i only needed to run the query once, but I'll save it for the future.
Thanks again
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply