Menu
I am trying to populate any rows missing a value in their InterfaceID (INT) column with a unique value per row.
I'm trying to do this query:
UPDATE prices SET interfaceID = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) WHERE interfaceID IS null I was hoping the the (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) would be evaluated for every row, but its only done once and so all my affected rows are getting the same value instead of different values.
Can this be done in a single query?
322
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
declare @i int = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) update prices set interfaceID = @i , @i = @i + 1 where interfaceID is null should do the work
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
