0

I have 2 tables (let's call them OriginalTable and NewValuesTable) with several columns. I am going to discuss only about the column in question.

Pallet ID - Primary key of OriginalTable

OriginalTable has around 35000 entries where the PalletID value is wrong. My NewValuesTable has the right PalletID values. Here is my issue:

  • Let's take a sample set of the OriginalTable

enter image description here

  • These values are all wrong. These need to be replaced with new values from the NewValuesTable. Like this:

enter image description here

  • I don't know if the issue is obvious, but let me explain. The highlighted fields are the issue. When I update 21 to 22, there will be a PrimaryKey Constraint related error because it is a duplicate key. Likewise with 34 and 68. How do I go around this issue?

This is what I thought: Maybe edit the values that have a Duplicate Key exception to something like W**. For instance, 22 in the NewValue table becomes W22, 34 becomes W34. Add a W or something like that. Then update the table with these values. Then update the OriginalTable's PalletID field to remove the W.

Does that seem like a smart way to go about this? Is there a better way to do this?

Improve this question
13
  • Are you updating the rows one at a time? Do the two tables have the same number of rows? Are they are both ordered correctly even though one table needs updating? Commented Apr 3, 2017 at 17:40
  • I am updating nearly 35,000 rows using an update and join. They are ordered, yes. But the PalletID field does not have as simple a value as in the question. A typical PalletID looks like H2333431. The NewValues table has 35,000 rows because those are the ones that need changing. The OriginalTable has over 100,000 rows, but only the 35,000 rows with wrong PalletID are in question. Commented Apr 3, 2017 at 17:42
  • 1
    If you are updating all the rows at once there should not be any problem. Commented Apr 3, 2017 at 17:44
  • Can you mess around with the table structure? Commented Apr 3, 2017 at 17:45
  • How do you mean? What part of the structure? Depends on which table you are talking about. if I am messing with NewValuesTable, yes. OriginalTable, no. Commented Apr 3, 2017 at 17:46

2 Answers 2

Reset to default
1

A potential issue with updating the rows with a prefix is that, if anything goes wrong you are left with prefixed primary keys since it would be a constraint violation to update them to the new value. You are essentially disabling the constraint when populating it which opens up risks for that population.

I would build an identical empty table (include any constraints) prefixed with something to identify it as a non-production table. Then fill it with the correct values. Once you verify that all the data is correct, change the table names in a single statement. Just make sure there are no running transactions against the original table before updating the names.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I was really close to doing the prefix method. You actually saved me from that. I have done that once in the past and struggled with it, I completely forgot. That is a good idea. I will see if anyone else has anything useful and if not, probably proceed with yours. Thanks.
1

Here's some SQL to demonstrate how you can update as you wish all in one go. I've included some extra bits of script which should allow you to better test your update SQL on your test environment without ruining your test data:

set xact_abort on -- make sure it rolls back if error go create table #t1 (link int primary key, id char) create table #t2 (link int, id char) insert into #t1 select 1, 'a' union select 2, 'b' union select 3, 'c' insert into #t2 select 2, 'c' union select 3, 'd' BEGIN TRAN select * from #t1 select * from #t2 select * from #t1 a left join #t2 b on a.link = b.link UPDATE #t1 set id = b.id from #t1 a join #t2 b on a.link = b.link select * from #t1 ROLLBACK --commit drop table #t1 drop table #t2
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.