Brad,
Quote:>AB807
By the looks of this variable name I am guessing you have the
misfortune to be trying to use relation operations (SQL) against data
from JD Edwards. You have my sympathy. When I last worked with data
from their system they had not yet reached the stage of understanding
that every table needs a primary key.
Quote:>I have SQL2000 using SEM. Trying to edit a row in my deptdata table. Each
>time I try to update a NULL value, or delete the row, it gives me this
>error:
>Key column information is insufficient or incorrect. Too many rows were
>affected by update.
The only way this can work is if the table has a primary key or unique
index.
Quote:>I have no primary key set in this table.
So that fits.
Quote:>When I tried to set a primary key,
>it gave me this error:
>'deptdata' table
>- Unable to create index 'PK_deptdata'.
>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
>INDEX terminated because a duplicate key was found for index ID 1. Most
>significant primary key is 'AB807'.
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
>See previous errors.
>[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>terminated.
So you have non-unique data in your intended key.
Quote:>The row that contains the data 'AB807' is the same row that gave me the
>first error that I presented in this posting.
Lets approach this in simple SQL run in Query Analyzer. Start with
something like:
SELECT AB807, count(*) as rows
INTO #keys
FROM AddressBook
GROUP BY AB807
HAVING count(*) > 1
SELECT * FROM #keys
This will show you all the values of AB807 that are stopping the key
from being created. Now look at the complete rows that match the
problem.
SELECT *
FROM AddressBook
WHERE AB807 IN (select AB807 from #keys)
This shows you everything about the problem row(s).
Now for the real work. Each value of AB807 that has a problem has two
or more rows in the result set just returned. If the rows are
absolutely identical - in ALL columns - you have to approach the
problem one way, which I'll get to shortly. If there are differences
in another column between (or among) the rows with the same value of
AB807 it is simpler, so lets deal with that first.
Suppose the column AB888 is also in the table, and there are two rows
returned with the same AB807, but different values of AB888. Pick the
value of AB888 you want to get rid of and use it to write a select
that shows just that row:
SELECT *
FROM AddressBook
WHERE AB807 = 'value you already knew'
AND AB888 = 'value you just figured out'
If you can write a select like this, even if it must test five or ten
columns for specific values, and the select returns JUST the row(s)
you must delete, you are in good shape. Once it looks right you can
turn this into a DELETE:
DELETE
FROM AddressBook
WHERE AB807 = 'value you already knew'
AND AB888 = 'value you just figured out'
Or maybe the rows are completely duplicated. In this case, see the
FAQ entry deleteduprows.txt at http://www.sqlserverfaq.com/.
Good luck!
Roy
by