I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
The table has the schema
Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL, Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7 varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL, Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL, Col14 float NULL, Col15 float NULL )
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microso ft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO. I vaguely remember hearing somewhere that delete causes a lot of problems if the table doesn't have primary keys. Is this correct?
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
The table has the schema
Table1( Col1 varchar(50) NULL, Col2 varchar(50) NULL, Col3 int NULL, Col4 int NULL, Col5 varchar(128) NULL, Col6 varchar(128) NULL, Col7 varchar(100) NULL, Col8 varchar(50) NULL, Col9 varchar(50) NULL, Col10 int NULL, Col11 int NULL, Col12 float NULL, Col13 float NULL, Col14 float NULL, Col15 float NULL )
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microso ft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO. I vaguely remember hearing somewhere that delete causes a lot of problems if the table doesn't have primary keys. Is this correct?
Comment