1

Let's assume my table is the following:

id | name | country
--------------------
   | John | USA
   | Mary | USA
   | Mike | USA

Someone can help me with a script that can add id's to all names?

Thanks

daniele3004
  • 13,072
  • 12
  • 67
  • 75
cdrrr
  • 1,138
  • 4
  • 13
  • 44
  • Simplest option is to use cursor and update each row. – Paresh J Oct 08 '14 at 06:30
  • from where you are getting id ? is it random or auto incremented ? – Sunil Chhimpa Oct 08 '14 at 08:12
  • @PareshJadhav while a cursor may *seem* to be an easy solution, it does not scale well to a large table. Databases do not generally work well with loops and a set-based operation like the ones suggested in the answers will perform *much* better on non-trivial table sizes. – alroc Oct 08 '14 at 11:18

2 Answers2

2
-- Create a temporary table for the example.
CREATE TABLE #People(Id int, Name nvarchar(10), Country nvarchar(10))

-- Insert data, leaving the Id column NULL.
INSERT INTO #People(Name, Country) SELECT
    'John', 'USA' UNION ALL SELECT
    'Mary', 'USA' UNION ALL SELECT
    'Mike', 'USA';

-- 1. Use Row_Number() to generate an Id.
-- 2. Wrap the query in a common table expression (CTE), which is like an inline view.
-- 3. If the CTE references a single table, we can update the CTE to affect the underlying table.
WITH PeopleCte AS (
    SELECT 
        Id, 
        Row_Number() OVER (ORDER BY (SELECT NULL)) AS NewId 
    FROM 
        #People    
)
UPDATE PeopleCte SET Id = NewId

SELECT * FROM #People

DROP TABLE #People
Evil Pigeon
  • 1,887
  • 3
  • 23
  • 31
  • Is the base table PEOPLE? and shouldn't it be Select From base table after updation? – Paresh J Oct 08 '14 at 06:32
  • I've updated with comments to clarify. People (now PeopleCte) is a common table expression. You can generate the ID and perform the update in a single query this way. #sqlservermagic – Evil Pigeon Oct 08 '14 at 08:16
0

try this

update table set a.id=b.newid from  table a,(select row_number() over (order by (select null)) newid,name from #temp) b

make changes like ordering as needed

Recursive
  • 954
  • 7
  • 12