solution1368
asked on
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
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
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
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 YourTableASKER
The table already existed so "Create" Table synatx won't work.
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.
>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.
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 YourTableASKER
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.
>i can't drop the table.
Just modify the UPDATE statement to match your table and column names, and execute it.
Just modify the UPDATE statement to match your table and column names, and execute it.
select id, productname,
row_number() over (order by id) + 41 as id2
from table
row_number() over (order by id) + 41 as id2
from table
ASKER
awking00: Can you show me how to do it in completed codes?
I basically need to add a column, not just select thanks
I basically need to add a column, not just select thanks
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.
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_orderASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER