Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PF79
Starting Member
3 Posts |
 Posted - 2010-08-11 : 16:54:06
|
| Hi,I am currently trying to find the quickest way to update a particular table that I have.The table has 5 fields, 4 of which are used in the Primary Key ([Item No_], [Variant Code], [Location Code] & [Valuation Date])The problem that I am currently having is trying to update the [Location Code] field.I need to update all blank locations with a particular value. The problem being that the location that I am using in the SET already exists and so there might already be entries in the table where every primary key value apart from the Location is identical.Calling the following code:UPDATE [table]SET [Location Code] = 'DDC'WHERE [Location Code] = ''generates errors stating that the Primary Key will be violated because the record already exists.I tried adding another clause to the WHERE:WHERE [Location Code] = '' AND NOT EXISTS(SELECT 1 FROM [table] WHERE [Item No_] = [Item No_] AND [Variant Code] = [Variant Code] AND [Location Code] = 'DDC' AND [Valuation Date] = [Valuation Date])this then states that no rows have been updated.The table in question contains 900,000 records.I have also tried using a CURSOR and checking whether the individual records already exist but this has proved far to slow to use with a table of this size.Can anyone think of a better way of achieving this???Many thanksPF79 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2010-08-11 : 17:08:58
|
Your issue is that you can only have 1 unique primary key value in the table.The table you are updating seems to have LocationCode as the PrimaryKey, which I am not sure if you intended, but the Primary key has a UNIQUE constraint, meaning that if there is already a 'DDC' value, then you can not update another record to have the same value.This is the code you would need to use.UPDATE [table]SET [Location Code] = 'DDC'WHERE [Location Code] = ''and not exists (Select * from table where [location code] = 'DDC')It seems like that is not what you want though, Make sure you have properly setup the primary key to be a UNIQUE Column, and not a column that you want to be able to have duplicate values in.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
PF79
Starting Member
3 Posts |
Posted - 2010-08-11 : 17:30:14
|
| Hi Vinnie881,Thanks for your swift reply. The [Location Code] needs to be part of the Primary Key as the same Item, Item Variant and Valuation Date could be used across multiple Locations. There is no one column that contains a unique reference which is why it is spread over all 4 columns.In the example that I gave I did include a NOT EXISTS clause in the WHERE. I thought that where I specified [Item No_] = [Item No_] that the left hand one would be from the enclosed SELECT statement and the right hand [Item No_] would come from the UPDATE statement. Is this not correct?ThanksPF79 |
|
|
|
PF79
Starting Member
3 Posts |
Posted - 2010-08-11 : 17:43:35
|
| Hi All,I have managed to solve this by using the following code:UPDATE [table1]SET [Location Code] = 'DDC'WHERE [Location Code] = '' AND NOT EXISTS( SELECT 1 FROM dbo.[table1] AS table2 WHERE table2.[Item No_] = [table1].[Item No_] AND table2.[Variant Code] = [table1].[Variant Code] AND table2.[Location Code] = 'DDC' AND table2.[Valuation Date] = [table1].[Valuation Date])Thanks |
|
|
|
|
|
|
|
|
UPDATE WHERE NOT EXISTS