512 error with update statement( and no triggers)

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

General SQL Server Forums

New to SQL Server Programming

512 error with update statement( and no triggers)

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-07-05 : 07:07:25

Hello there. I am getting the bellow error when trying the following statement,.

begin tran

update dbo.f__Transactions set Currency =(

select REPLACE ([GoodsValueInDocumentCurrency] , '-', '+')
from dbo.f_Nominal_Transactions

where Currency < 0)

and i get the following error.

Msg 512, Level 16, State 1, Line 3
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.

but i can not find any triggers. whatelse can it be.??

Regards

Rob

MCTS / MCITP certified

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 07:15:59

The error message pretty much says what the problem is. The subquery [ the part that starts with select REPLACE(....] returned more than one row. That is not allowed.

If you are trying to update the dbo.f__Transactions set Currency table with data from dbo.f_Nominal_Transactions then, a statement such as this may be what you need:

UPDATE t SET
Currency = REPLACE ([GoodsValueInDocumentCurrency], '-', '+')
FROM
   dbo.f__Transactions t
   INNER JOIN dbo.f_Nominal_Transactions n ON
      -- you need a join condition here. What relates the two tables?
      -- t.Currency_Name = n.Currency_Name -- perhaps?
WHERE
   t.Currency < 0;

Obviously I am guessing here. What are the data types of GoodsValueInDocumentCurrency and Currency? Can you provide some sample data? Also, can you describe what you are trying to do?

Also, take a look at Brett's post here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx which describes how to post table DDL.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-07-05 : 07:18:50

o really sorry. was sortening the column names and forgot one of them

the True sytax is

begin tran

update dbo.f__Transactions set Currency =(

select REPLACE ([Currency] , '-', '+')
from dbo.f_Nominal_Transactions

where Currency < 0)

the datatype for currency is numeric(18,2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-05 : 07:35:53

update dbo.f__Transactions set Currency = -Currency
where Currency < 0

update dbo.f__Transactions set Currency = abs(Currency)
where Currency < 0

N 56°04'39.26"
E 12°55'05.63"

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-07-05 : 07:47:40

all i am trying to do is replace all the negative figures ( -344.90 ) and replace them with + ie ( +344.90)

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-07-08 : 07:26:42

can any one suggest how i would perform an update getting around the below error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-08 : 07:28:11

I did...
See my response dated 07/05/2011 : 07:35:53.

Since you are using correct datatype, there is no visible plus sign in the column. Only a minus sign for negative values.

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 07:33:03

If you are trying to change the - to + in the SAME table, use exactly what Peso posted.

If you want to update the dbo.f__Transactions using the values in dbo.f_Nominal_Transactions and at the same time change the minus to plus, you can use the query I had posted.

But, if you want use what I posted, you will need to define how the two tables relate to each other - i.e., which row in the target table matches which row in the source table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources