sql query help

have MS SQL table structure like below and want to add value start value = 42, and keep increment them. how can i do that? I have the following sample for your research.

Thanks


Before

table
ID productname
1   product Z
2   product Y
3   product X

After

table
ID product name   ID
1   product x          42
2   product y          43
3   product z          44
solution1368Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

solution1368Author Commented:
Just FYI, we have over 10,000 rows in that table. so static insert will not work. It must be increment. thank
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
-- In case the column start_value is not already in the table..
ALTER TABLE YourTable
ADD start_value int   -- or whatever data type ID is. 

-- Generate the start_value numbers
UPDATE YourTable
SET start_value = 41 + ID

Open in new window

btw a table can't have two columns named ID, so I named the other one start_value
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just in case the ID's are not sequential, use RANK to determine the seqential order, and update start_value with that number
CREATE TABLE YourTable (ID int, productname varchar(50), start_value int)

INSERT INTO YourTable (ID, productname) 
VALUES 
	(1, 'product Z'), (2, 'product Y'), (3, 'product X'), (4, 'product Y'), (5, 'product X'),
	(7, 'product Y'), (8, 'product X'), (10, 'product Y')

UPDATE YourTable
SET start_value = ytr.id_rank_order 
FROM (
	SELECT id, RANK() OVER (ORDER BY id) as id_rank_order
	FROM YourTable yt) ytr
	JOIN YourTable yt ON ytr.id = yt.id

SELECT * FROM YourTable

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

solution1368Author Commented:
The table already existed so "Create" Table synatx won't work.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You'll want to copy-paste the above into your SSMS and execute it to verify that it works per your requirements.  Then delete the stuff that is not required (e.g. CREATE TABLE) and modify the table and column names to match your needs.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The table already existed so "Create" Table synatx won't work.
Just for executing it in SSMS, add the IF..EXISTS block to your code, so that if it is executed more than once it does not return a 'Table name already exists' error message.
IF EXISTS(SELECT name FROM sys.tables WHERE name='YourTable')
   DROP TABLE YourTable
GO

CREATE TABLE YourTable (ID int, productname varchar(50), start_value int)

INSERT INTO YourTable (ID, productname) 
VALUES 
	(1, 'product Z'), (2, 'product Y'), (3, 'product X'), (4, 'product Y'), (5, 'product X'),
	(7, 'product Y'), (8, 'product X'), (10, 'product Y')

UPDATE YourTable
SET start_value = ytr.id_rank_order 
FROM (
	SELECT id, RANK() OVER (ORDER BY id) as id_rank_order
	FROM YourTable yt) ytr
	JOIN YourTable yt ON ytr.id = yt.id

SELECT * FROM YourTable

Open in new window

0
solution1368Author Commented:
i can't drop the table. It has too many record there, and I don't want to manually input them again inside of the sql statement.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i can't drop the table.
Just modify the UPDATE statement to match your table and column names, and execute it.
0
awking00Commented:
select id, productname,
row_number() over (order by id) + 41 as id2
from table
0
solution1368Author Commented:
awking00: Can you show me how to do it in completed codes?
I basically need to add a column, not just select thanks
0
SharathData EngineerCommented:
1. Do you already have a column to hold this new id? If not, you have to fist alter your table to add the column.
2. run the update statement to popualte this new column with values starting from 41.
 ;with cte as (
 SELECT *, ROW_NUMBER() OVER (ORDER BY id)+40 as id_rank_order
	FROM YourTable)
update cte set start_value = id_rank_order

Open in new window

jimhorn tried to help you a lot but I think you are not following him.
0
awking00Commented:
alter table yourtable add(id2 number);

uopdate yourtable y set id2 =
(select id2 from
 (select id, row_number() over (order by id) + 41 as id2
  from yourtable) x
 where x.id = y.id);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.