CODEWITHSUNDEEP
sqlsql-server
Andriy Zakharko
Andriy Zakharko

Reputation: 1673

Update existing schema using cursor

I'm trying change existing schema to add new column.

Here is my case - http://sqlfiddle.com/#!3/06c58/1

Value of Order column is int, which should represent order of items on UI from 1 to COUNT(Id) if I'm grouping TemplateId.

I see 2 possible solutions - make Order calculated field or use Cursor to update it.

Which one is better? And if update using cursor win -

May I know how to have such update ?

Upvotes: 0

Views: 81

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

One way I can think of it is this:

  1. Start a transaction
  2. Insert into columns apart from Order
  3. Update the table to set Order values
  4. Commit transaction if everything is OK, else rollback.

You won't need to directly deal with cursors to do this, and the operation will be atomic in a way, since it's wrapped in a transaction block.

The update itself can be done something like this:

;with ord as 
(Select id, row_number() over (partition by templateid order by selectedfieldid) rn from yourtable)
Update dbo.titlefields
Set order = rn - 1 --ROW_NUMBER() starts at 1 but we want to start at 0
From dbo.titlefields t inner join
ord x on t.id = x.id

Please let me know if I have understood your requirement correctly, and I will update my answer if that is not so.

Upvotes: 1

Related Questions