how to update a column for all rows in a table with different values for each row in a table of 100 records
my table contains 100 records and i need to update a column with new values(diff value for each record) fo开发者_Python百科r each of the 100 records .How can i do this. the column to update the values is not primary key.
UPDATE tablename
SET columnname = CASE id
WHEN 1 THEN 42
WHEN 2 THEN 666
END
With this query columnname
will be updated to 42
for the row with id = 1
, and 666
for id = 2
Create a table with an autoicrement id and the columns of the original table.
Then
INSERT INTO new_table (column1, column2,.....) -- refer all columns except autoincrement id
SELECT * FROM old_table
Update the old table by joining with the new, assuming the is a key composite or not that distincts each row
Set Unique constraint on this column.
ALTER TABLE YourTableName
ADD CONSTRAINT uc_ColumnID UNIQUE (ColumnName)
Now, whenever you try to update it with duplicate values, sql server will not allow:)
Also a long run scenario
If you're on SQL Server 2005 or newer (you didn't exactly specify.....), you could easily use a CTE (Common Table Expression) for this - basically, you select your PK value, and a counter counting up from 1, and you set each row's ColumnName
column to the value of the counter:
;WITH UpdateData AS
(
SELECT
PKValue,
ROW_NUMBER() OVER(ORDER BY .......) AS 'RowNum'
FROM
dbo.YourTable
)
UPDATE dbo.YourTable
SET ColumnName = u.RowNum
FROM UpdateData u
WHERE dbo.YourTable.PKValue = u.PKValue
With this, you're generating a sequence from 1 through 100 in the RowNum
field of the CTE, and you're setting this unique value to your underlying table.
load a DataTable say dt with specific row ID of the table which you wanna update. then execute
foreach(DataRow rw in dt.Rows)
{
update table_name set column_name=desired_value where specific_column=rw
}
精彩评论