Re: Multiple records found, but only one was expected -
MSSQL

> Hi All,
> I have hit a problem where I cannot delete a duplicate row - MSSQL2000.
> When I try to, it gives the error "Multiple records found, but only
one
> was expected ". I can add as many duplicate rows as I want but I
can't
> delete or modify them.
> tia
> Trevor

FWIW that is like the error you get in dBase SQL when a query retrieves 
multiple rows for an operation that needs (expects) one row. For example if 
you are doing an update query like this:
Update table1 T set T.field1 = (select S.field2 from table2 S where S.field3 
= T.field4)
If the subquery returns several values for a specific row in table1 it can't 
update field1 in that row.
But how would a deletion could create that scenario?
E.L. 

In article <MPG.21f69f3f6036ecf598969c@news.dbase.com>, 
trannnospam@nospammpx.xom.au says...
> Hi All,
> I have hit a problem where I cannot delete a duplicate row - MSSQL2000. 
> When I try to, it gives the error "Multiple records found, but only
one 
> was expected ". I can add as many duplicate rows as I want but I can't

> delete or modify them.
> 
> tia
> 
> Trevor

 
Trevor,

How are you trying to do the delete? Is it like this:

delete from yourTable where yourUniquePrimaryKey = 999

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Trevor schrieb:
> I can add as many duplicate rows as I want but I can't 
> delete or modify them.

You've just hit one of the basics of backend-server usage.


EVERY table needs a unique identifier, usually a primary key.
Even DBFs do.

It's just that DBFs have a "native" and automatically implemented 
primary key.... the record number!

You can opt to define an additional PK, and you should, but you don't 
have to.

With SQL servers it is different. You really SHOULD give a PK to EVERY 
table, be it even very small in number of fields and records.
SQL servers do not hold record numbers.

In fact they are physically doing a constant re-ordering for reasons of 
optimization.


In your case, the server can just not know which record to delete or 
modify, because your definition to find it is too unspecific. Bad luck 
for you, the definition is as good as you can do, I guess.

Here is one solution.
Add a new field. Numeric would be good (Int64). Copy the complete table 
into dBASE (I mean what I write: DBF!)
There they will get temporary record-numbers and you can modify the records.
Insert an incrementing number into the new field.
Export it back to MSSQL format.
Define the column as non-nullable and put the primary key on it.

good luck!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL servers are really easy to handle, just remember PPP
P:	a _P_ rimary key for EVERY table
P:	_P_ arametrize as many queries as you can
P:	use _P_ rocedures on the server to replace local functions

Hi All,
I have hit a problem where I cannot delete a duplicate row - MSSQL2000. 
When I try to, it gives the error "Multiple records found, but only one 
was expected ". I can add as many duplicate rows as I want but I can't 
delete or modify them.

tia

Hello Andre,
P: _P_ arametrize as many queries as you can
I know what a parameter is
I know what a query is
But I am not sure I understand what you mean by P: _P_ arametrize as many 
queries as you can.
Can you explain a little please?

Greg Hill




> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SQL servers are really easy to handle, just remember PPP
> P: a _P_ rimary key for EVERY table
> P: _P_ arametrize as many queries as you can
> P: use _P_ rocedures on the server to replace local functions
>
>