Update query error - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query error

Author

Topic

marrio
Starting Member

4 Posts

Posted - 2007-09-21 : 05:06:07

Hi - I am very new to SQL2005 and am trying to run the following example:

UPDATE BB_CallActivity
SET DevicePin = '24FCC75D'
WHERE (DevicePin = '25015075')

to update a column where multiple rows contain a 'devicepin' that needs to be changed to a new device pin. If I test the code it works correctly but when I execute I get the following message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Your help would be much appreciated!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-21 : 05:14:35

Hi
Could you please post some sample data?

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 05:18:27

Can you post the full query you used?

Madhivanan

Failing to plan is Planning to fail

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-21 : 05:45:28

The above statement shouldn't throw that sort of error.

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 05:50:35

The above statement can't throw that sort of error.

Edit: Actually I suppose it might if BB_CallActivity is a View, with some dodgy sub-select it in!

marrio
Starting Member

4 Posts

Posted - 2007-09-21 : 08:48:42

Hi - thanks for looking. Test data is below thought there are many more columns in the live data:

CallID AccNo. Devicepin DateTime
2658 test 203FEEFD 47:38.9
2836 test 203FEEFD 25:39.4
2837 Test1 25015085 25:40.2
2838 test 2 203FEEFD 25:41.3
2839 testW 25014B07 26:56.6
2840 Terst2 25014B07 26:58.3
2841 test2 25014B07 28:30.3
2842 test4 25014B07 30:20.2
2843 test 25014B07 31:11.4
2844 Test_M 25015085 34:06.0

The query is exactly as pasted and I have the correct database selected. As I mentioned, test 'tick' reports no errors but when I execute it against the table I get the error. The primary key is the call ID.

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 08:59:03

Your values for DateTime aren't valid. I just deleted the first digit



CREATE TABLE BB_CallActivity
(
	CallID int NOT NULL,
	AccNo varchar(10) NOT NULL,
	Devicepin varchar(10) NOT NULL,
	[DateTime] DateTime NOT NULL,
	PRIMARY KEY
	(
		CallID
	)
)

INSERT INTO BB_CallActivity
SELECT	2658, 'test', '203FEEFD', '19000101 7:38.9' UNION ALL
SELECT	2836, 'test', '203FEEFD', '19000101 5:39.4' UNION ALL
SELECT	2837, 'Test1', '25015085', '19000101 5:40.2' UNION ALL
SELECT	2838, 'test 2', '203FEEFD', '19000101 5:41.3' UNION ALL
SELECT	2839, 'testW', '25014B07', '19000101 6:56.6' UNION ALL
SELECT	2840, 'Terst2', '25014B07', '19000101 6:58.3' UNION ALL
SELECT	2841, 'test2', '25014B07', '19000101 8:30.3' UNION ALL
SELECT	2842, 'test4', '25014B07', '19000101 0:20.2' UNION ALL
SELECT	2843, 'test', '25014B07', '19000101 1:11.4' UNION ALL
SELECT	2844, 'Test_M', '25015085', '19000101 4:06.0'

UPDATE BB_CallActivity
SET DevicePin = '24FCC75D'
WHERE (DevicePin = '25015075')
GO

DROP TABLE BB_CallActivity
GO

(0 row(s) affected)

Posting a proper worked example that demonstrated your problem would save folk here time.

Kristen

marrio
Starting Member

4 Posts

Posted - 2007-09-21 : 10:32:25

Hi - I don't need to create the table 'BB_CallActivity' as it already exists and has 2000 records. About 1/5th of those records have the incorrect 'devicepin' that needs to be changed to the new devicepin (all other columns should remain as they are).

Essentially I need a 'find and replace' which my query looks like it should do. Unforetunately I get the aforementioned error. There are 52 columns in all - for this example I guess only the first three are relevant.

Thanks!

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:11:20

"I don't need to create the table 'BB_CallActivity' as it already exists"

Yes but WE do in order to reproduce your problem and suggest a solution.

If you can't be bothered to provide a self contained example of your problem don't expect much help from here!

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 11:26:22

Please check and insure that BB_CallActivity is in fact a table and not a view or something. Find it in SQL Server Management Studio and right click it and Script it. Paste the generated script in a message here.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 11:27:34

FYI...I do this and it runs just fine so it's not the Update statment.

CREATE TABLE BB_CallActivity
(
CallID int NOT NULL,
AccNo varchar(10) NOT NULL,
Devicepin varchar(10) NOT NULL,
[DateTime] DateTime NOT NULL,
PRIMARY KEY
(
CallID
)
)

INSERT INTO BB_CallActivity
SELECT 2658, 'test', '203FEEFD', '19000101 7:38.9' UNION ALL
SELECT 2836, 'test', '203FEEFD', '19000101 5:39.4' UNION ALL
SELECT 2837, 'Test1', '25015075', '19000101 5:40.2' UNION ALL
SELECT 2838, 'test 2', '203FEEFD', '19000101 5:41.3' UNION ALL
SELECT 2839, 'testW', '225015075', '19000101 6:56.6' UNION ALL
SELECT 2840, 'Terst2', '25014B07', '19000101 6:58.3' UNION ALL
SELECT 2841, 'test2', '25014B07', '19000101 8:30.3' UNION ALL
SELECT 2842, 'test4', '25015075', '19000101 0:20.2' UNION ALL
SELECT 2843, 'test', '25014B07', '19000101 1:11.4' UNION ALL
SELECT 2844, 'Test_M', '25015075', '19000101 4:06.0'

UPDATE BB_CallActivity
SET DevicePin = '24FCC75D'
WHERE (DevicePin = '25015075')
GO

DROP TABLE BB_CallActivity

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:32:07

Except that it also doesn't update any rows, doesn't therefore match any rows, and the data provided is probably therefore not representative of the problem! Not least of which that the DateTime values originally provided were not valid to import ...

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-21 : 11:41:28

Well, I changed a couple of the values so that it updated 3 rows. So it's not an issue with the Update having trouble updating multiple rows.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 02:08:08

<<
This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
>>

Again, Post the exact query you used

Madhivanan

Failing to plan is Planning to fail

rohan_man
Starting Member

8 Posts

Posted - 2007-09-25 : 20:43:50

Hey Marrio,

I'm new to this too, and had a similar problem, when I was doing a simple update statement.

I can't claim credit to have solved the problem myself, but found other helpful people on the net that suggested another solution...... the the problem may lie in a trigger on the table. Check the table and disable any triggers that are on it, run your update statement, and then re-enable those triggers.

Here is the link if you wanted to see what they said.
http://p2p.wrox.com/topic.asp?TOPIC_ID=15359

Cheers
Rohan

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:09:23

Yes, good point rohan_man, it could well be a trigger.

But I would caution disabling the trigger as its probably there for a very good reason!

sp_helptrigger 'MyTable'

will give you a list of any triggers on MyTable, and

sp_helptext 'MyTriggerName'

will show you the source code of a trigger.

Kristen

marrio
Starting Member

4 Posts

Posted - 2007-09-26 : 14:47:07

Hi - thanks rohan_man (and everyone else who has offered me help) - it was indeed a trigger causing the problem. I will investigate the trigger and only disable when I need to update.

You've really helped me out!

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 15:28:55

Good catch rohan_man !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources