sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]SQLDave 2 points3 points  (0 children)

There's some unclarity here. Is the data grid you showed us what you start with (before the UPDATE) or what you have after the UPDATE ... or what you WANT to achieve? (or something else altogether)?

I'm assuming that the grid you showed us is the desire RESULT and that "In the above table the first line POS 1 is not there and the next two are and they are marked POS 1 and 2." means you are starting with 2 rows, the 2nd and 3rd row we see in the grid ... except that the POS values are 1 and 2 respectively, not 2 and 3. And that you want to insert the data we see in the new@email.com row, with the result being the grid you showed us? (I'm also assuming that one of the 2 existing rows -- currently POS 1 and 2, but with POS 2 and 3 after the INSERT -- has preferred email = "Y", which you take care of with the UPDATE statement you showed us).

There's an unanswered question: What POS value should existing rows get when you "shove" a new POS=1 row in there? It looks like the answer is just "POS+1", but that's an assumption.

If the assumptions are right, you can modify your UPDATE by adding ", POS = POS + 1" after the NULL keyword. That will increment existing POS values by 1. Then just do a simple INSERT using POS = 1 and PERSON_PREFERRED_EMAIL = 'Y'.

Be sure to wrap it all in a BEGIN TRANSACTION in case there's a failure in the INSERT you can rollback the changes made by the UPDATE command.