Update row data with a new value per row using fluentmigrator

Question

I am using fluentmigrator to add a new column to a table. I then want to update each row in the table with a unique value for that column.

Currently when I use:

Update.Table("Foo").InSchema("dbo").Set(new { Bar = Bar.Generate() }).AllRows();

It gives the same value for all the rows.

How do I ensure it calls that method for each row?

Getty

Answer 1

I'm not sure what Bar.Generate does but I am guessing it creates a GUID or unique id.

If so then you could use:

Execute.Sql("update dbo.Foo set Bar = NEWID()");

Or if you want sequential guids then you could use NEWSEQUENTIALID().

If you are adding a new column for this unique identier, then all you would need to do is specify the new column .AsGuid()

EDIT: FluentMigrator is a small fluent dsl and is not meant to cover a complicated case like this. There is no way (as far as I know) to do this with one sql UPDATE and therefore no easy way to do it with FluentMigrator. You'll have to get the row count for the table with ADO.NET or an ORM (Dapper/NHibernate) and then loop through each row and update the Bar column with the custom unique identifier. So if you have one million rows then you will have to make one million sql updates. If you can rewrite your Bar.Generate() method as an Sql function that is based on the NEWID() function like this or this then you could do it as one UPDATE statement and call it with FluentMigrator's Execute.Sql method.

You haven't mentioned which database you are working with. But some like Postgres have non-standard features that could help you.

Protractile

its my own function that creates a unique "friendly" string, sorry not a guid. – Getty

Can you rewrite your function as an SQL function? Otherwise you'll have to loop through all the rows in your table. – Protractile

It should be noted that in your code example, the keyword table should not be there, the command won't run. Replace with your actual table name. – Modesta