1

I'm introducing a primary key column to a table that doesn't have one yet. After I have added a normal field Id (int) with a default value of 0 I tried using the following update statement to create unique values for each record:

update t1
set t1.id = (select count(*) from mytable t2 where t2.id <> t1.id)
from mytable t1

I would expect the subquery to be executed for each row because I'm referencing t1. Each time the subquery would be executed the count should be one less but it doesn't work.

The result is that Id is still 0 in every record. I have used this before on other DBMS with success. I'm using SQL Server 2008 here.

How do I generate unique values for each record and update the Id field?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ronald
  • 171
  • 1
  • 4
  • Why aren't you just adding a `ID INT IDENTITY(1,1)` column?? Then SQL Server will automatically take care of creating unique values for `ID` and you don't have to update anything at all..... – marc_s Feb 06 '12 at 14:16
  • Thanks. I'll try. I work with many different DBMS so I always try the most generic approach. – Ronald Feb 06 '12 at 14:23

2 Answers2

4

Trying to explain why it doesn't work as you expect:

I would expect the subquery to be executed for each row because I'm referencing t1.

It is executed and it can affect all rows. But an UPDATE stetement is one statement and it is executed as one statement that affects a whole table (or a part of it if you have a WHERE clause).

Each time the subquery would be executed the count should be one less but it doesn't work.

You are expecting the UPDATE to be executed with one evaluation of the subquery per row. But it is one statement that is first evaluated - for all affected rows - and then the rows are changed (updated). (A DBMS may do it otherwise but the result should be nonetheless as if it was doing it this way).

The result is that Id is still 0 in every record.

That's the correct and expected behaviour of this statement when all rows have the same 0 value before execution. The COUNT(*) is 0.

I have used this before on other DBMS with success.

My "wild" guess is that you have used it in MySQL. (Correction/Update: my guess was wrong, this syntax for Update is not valid for MySQL, apparently the query was working "correctly" in Firebird). The UPDATE does not work in the standard way in that DBMS. It works - as you have learned - row by row, not with the full table.

I'm using SQL Server 2008 here.

This DBMS works correctly with UPDATE. You can write a different Update statement that would have the wanted results or, even better, use an autogenerated IDENTITY column, as others have advised.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Thanks for you elaborate reply. I was expecting the row by row behavior like you said (also in Firebird DBMS). – Ronald Feb 06 '12 at 15:18
  • Your account sounds horribly plausible :) But I have to ask: does the same update in mySQL really work as the OP expected? It's just that it is so not how SQL is supposed to work! – onedaywhen Feb 06 '12 at 15:20
  • You're absolutely right. I don't know about MySQL behavior in this situation. – Ronald Feb 06 '12 at 15:34
  • The query does not work as it is in MySQL (I was wrong about that). The syntax is different (`UPDATE ... FROM ... SET ...`) and you can't specify the same table both as the target of Update and inside the `WHERE` clause. – ypercubeᵀᴹ Feb 06 '12 at 15:48
  • @onedaywhen: This query does not work in MySQL. But MySQL's `UPDATE` works in non-standard way. You can't do for example `UPDATE t SET id = id+1` if `id` is Unique or Primary Key (you'll get duplicate Primary Key error). You can however do: `UPDATE t SET id = id+1 ORDER BY id DESC` (no duplicates this way which clearly shows that rows are updated and constraints checked row by row!) – ypercubeᵀᴹ Feb 06 '12 at 16:05
  • Columns are also updated from left to right (when in the same table), which is also non-standard behaviour. Reference here: `This behavior differs from standard SQL.` [MySQL UPDATE](http://dev.mysql.com/doc/refman/5.1/en/update.html) – ypercubeᵀᴹ Feb 06 '12 at 16:08
  • Thanks for confirming. I've not really used mySQL so I shouldn't judge but revelation such as these really put me off it! – onedaywhen Feb 06 '12 at 16:28
1

The SQL is updating every row with the number of records where the ID doesn't equal 0. As all the rows ID equal 0 then there are no rows that are not equal to 0, and hence nothing gets updated.

Try looking at this answer here:

Adding an identity to an existing column

Community
  • 1
  • 1
samjudson
  • 56,243
  • 7
  • 59
  • 69
  • Instead I used this script to update the Id field: `DECLARE count INT SET count = (SELECT COUNT(*) FROM MYTABLE WHERE ID = 0) WHILE (count > 0) BEGIN UPDATE TOP (1) MYTABLE SET ID=@count WHERE ID=0 SET count = (SELECT COUNT(*) FROM MYTABLE WHERE ID = 0) END` – Ronald Feb 06 '12 at 15:30