SQL Script to edit a field in a Table

Question

3.00/5 (2 votes)

See more:

SQL-Server-2008

Can anyone please help.

I have an SQL 2008 R2 table of customer records and need to edit one of the fields in each reocrd.
E.g

Table "Customers"
Field in each record "Analysis3"
My Code is

SQL

select * from SLCustomerAccount
Update SLCustomerAccount
Set AnalysisCode3= 'O21 Merchant/Commercial Shipping'
where AnalysisCode3 = 'C21 Merchant/Commercial Shipping'

I get this Error message

txt

(2291 row(s) affected)
Msg 512, Level 16, State 1, Procedure UpdateCustomerExport, Line 7
Subquery returned more than 1 value. This is not permitted when the
      subquery  follows =, !=, <, <= , >, >= or when the subquery
       is used as an expression.
The statement has been terminated.

Please help i have little hair and i am pulling more out by the minute.

Thank you
Stephen

Posted 13-Sep-12 23:11pm

Stephen Finch

Updated 17-Sep-12 4:04am

fjdiewornncalwe

v2

Add a Solution

Comments

__TR__ 14-Sep-12 6:12am

"Procedure UpdateCustomerExport" - The update statement looks ok, are you calling any stored procedure after the update statement?

Stephen Finch 14-Sep-12 6:34am

No that is it.

Stephen Finch 14-Sep-12 6:34am

PS thank you for your reply

Stephen Finch 17-Sep-12 6:17am

Does anyone have any other idea's

_Amy 17-Sep-12 10:10am

Can you write your full procedure here. I think from there only the error is coming. Here, this code seems to be fine.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Answer 1 · 2012-09-17T04:36:00

Are you sure that SLCustomerAccount is a table? If all you are doing is using the two statements you posted, I assume there is some "black magic" going on behind the scenes. Using a statment like the following lets one create a view from the execution of a stored procedure:

SQL

CREATE VIEW dbo.SLCustomerAccount
AS
SELECT TOP(100) PERCENT *
FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=NameOfTheDatabasse', 'exec UpdateCustomerExport);

There might be an issue with having a view like that updateable. Your first statement (the select statement) will work fine, but an update on such a view might not be valid.

BTW in your question you also mention some table called "Customer" which does not show up anywhere else in your statements.

Regards,

— Manfred

Mr. Mahesh Patel · Answer 2 · 2012-09-14T03:06:00

Solution 1

hi dear
try this

SQL

Update SLCustomerAccount
Set AnalysisCode3= N'O21 Merchant/Commercial Shipping'
where AnalysisCode3 = N'C21 Merchant/Commercial Shipping'

Posted 14-Sep-12 3:06am

Mr. Mahesh Patel

Comments

Stephen Finch 14-Sep-12 9:16am

This doesn't seem to work, i still get this message
Msg 512, Level 16, State 1, Procedure UpdateCustomerExport, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Mr. Mahesh Patel 14-Sep-12 9:37am

I have run the query exact written by you and believe me that it was run successfully and also take effect by my table.
I am using the SQLSERVER 2008 R2.
What version you are using

Stephen Finch 14-Sep-12 9:40am

Yes i am using the same version.

I can't make it out what am i doin wrong.

Stephen Finch 14-Sep-12 9:50am

as soon as i run the code it goes in to read only mode, but if i don't run code i can manually edit the data. Help!