May 7, 2008 at 10:29 am
Hello
I would like to update a field which is unique index.
If i update it with a value that already exists, i of course get an error that it's an unique index, and the transaction is stopped.
What I would like to do, is to skip the line that is causing the unique index error (don't update it), and move on to the next one, without rolling back or exiting the update .
How could I write that?
Thanks for your help!!
May 7, 2008 at 10:39 am
well - you could simply filter your update statement to not update those rows.
Or you could have a BEFORE UPDATE trigger that catches and prevents the "bad values" from being updated.
Sorry if I can't be more specific, but without seeing what you're doing it's hard to be specific.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 1:48 am
well actually, maybe if i give an example it will be clearer: 🙂
I have a clients table where each client has one product.
But sometimes, the product code was entered manually , so there is a wrong value in it.
I have a transcodification table of products, where for each wrong product code there is the right product.
However, sometimes 2 wrong products should be replaced by the same right product.
So whenever i update my clients table, if one client has 2 wrong products that should be replaced by the same right product, i get a unique index error.
What I would like to do, is that whenever in my update statement, i get a unique index error, that I skip the update of this line and move to the next one.
Can't it be done in transaction? with a cursor or something?
Can't I avoid triggers?
May 9, 2008 at 3:59 am
There is no fast way of doing this in one operation. If you use a cursor you will have to scan the whole index for every row being updated which would be pretty slow for a big table.
Instead I suggest you write a temp table with two columns 'index' and 'new_index'. Index will hold the unique index from the table you are updating, new_index will hold the new value from your mapping file.
Next you de-duplicate the temp table using 'new_index'
You can now use the temp table to carry out the real update knowing that no index errors can occur as you are effectively skipping the duplications.
If you declare the temp table as a table variable it should be pretty fast because the temp_table exists only in memory and you also only scan the index three times.
May 9, 2008 at 5:17 am
Hi,
To get/update non matching records you can use following
update table_a
set row = b.xxx
FROM table_a left outer join table_a as b
on table_a.xxx = b.xxx
where b.xxx is null
W.Lengenfelder
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply