| Author |
Topic |
|
doggi3
Yak Posting Veteran
62 Posts |
 Posted - 2004-09-24 : 05:10:09
|
| I tried to delete records in table. It gave me an error: Key column information is insufficient or incorrect. Too many rows were affected by update.What does it mean?- HELP - |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-24 : 05:20:51
|
| where are you issuing the delete? can you post your statement? |
 |
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 2004-09-24 : 05:24:51
|
| I am using SQL Server Enterprise Manager. I have a table called customer_list_for_prospect_prof$.I get the rows using this query:SELECT *FROM customer_list_for_prospect_prof$WHERE (pers_last_nm = 'Lim') AND (pers_give_nm = 'Roy')It gives me 2 identical rows. I want to delect one away. I just right-click, and select 'Delete'. However, I was given the error mentioned above.- HELP - |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-09-24 : 05:42:06
|
| aha...a table with no primary key/index!!!use a script in QA.....the less you use EM...the better...There's an article here for resolving/removing duplicates |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 2004-09-24 : 05:46:18
|
| Actually my table has primary key.- HELP - |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-24 : 05:49:33
|
| how about deleting the row using query analyzer?delete from mytable where pk=key |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 2004-09-24 : 05:56:17
|
| That will delete both rows right? I only want to delete one of them. The two rows are actually duplication.- HELP - |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-24 : 05:57:59
|
| but you said you have a primary key.insert into #tempselect distinct <fields> from table1delete from table1 where id=pkinsert into table1select <fields> from #temp |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 2004-09-24 : 06:07:20
|
| The table below is example:pers_last_nm pers_give_nm cust_id Lim Roy 1 Lim Roy 1 <- Duplication, I want to remove this away Tan Jonathon 2Jen, I dun quite understand what <fields> and #temp mean. Can you explain? Thx!- HELP - |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-24 : 06:12:10
|
since i don't know your fields, i just enclosed them with < >,  what i mean is that you just enumerate your fields, The #temp will create a local temporary table for your session. so if you close your qa this will be dropped.algorithm: select the data you want (use distinct so you'll get only 1 record),then drop the rows in your table, and copy the data you selected earlier on which are saved in #temp. |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 2004-09-24 : 06:48:56
|
| ThX~- HELP - |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-24 : 09:23:28
|
| "Actually my table has primary key."Then you can't have two identical rows!If you have just one instance of a duplicate, and there are only two rows, you could do:SET ROWCOUNT 1delete from mytable where pk=keySET ROWCOUNT 0 -- This is VERY importantThis will restrict the delete to only deleting one row, so the other will be left behindIf you want to be "safe" then do this insteadBEGIN TRANSACTIONSELECT * from mytable where pk=keySET ROWCOUNT 1delete from mytable where pk=keySET ROWCOUNT 0 -- This is VERY importantSELECT * from mytable where pk=keyIs you only see one row in the second resultset then issue the COMMIT command, otherwise issue a ROLLBACK commandKristen |
|
|
|
Ketelaars
Starting Member
1 Post |
Posted - 2004-10-14 : 09:01:09
|
| Use a delete query in the Query-Analyser.I had the same problem, dindn't solve it, but used the abovementioned solution |
|
|
|
|
Delete