Help! can't run Update in SQL 2005

I have a table name : sale there have some item about (SN(int,PK),...Invoice(nvarchar)....)

try run a update query in SQL 2005 :

update sale set Invoive = '99999' where SN in('1','2','3')

Get the error msg:

Msg 512, Level 16, State 1, Procedure Update_Cost, Line 6
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 run in SQL 2000+SP4 is OK!

what's wrong in SQl2005?

this is working for me,

or

just send us the full test data(Table structure + Test data), check is there some trigger there for this table? go for the same statment for other table, i mean update same fileds, or so.

|||

From your description, SN is integer. The single quotes around the integers should be removed.

UPDATE sale SET Invoice = '99999' where SN in (1, 2, 3)

Martin Poon [MVP - SQL Server]

|||

Thanks a lot!

Find the root cause! after check the DB find there have trigger with updat invoice!

thanks again!

HELP! Concatentation of 2 INT columns in SQL 2000

I am looking to do a concatentation of 2 integer columns without converting them to varchar in SQL Server 2000. If you do a concatentation operator(+) on two integer columns it will try to do math instead of concatenation. Does anyone know how to do this without doing a cast or convert statement? I am asking due to performance issues.
Here is the statement that I am trying to change:

select
cast(a.so_id as varchar ) + cast(a.line as varchar) cust_db_shipment_key,
MULTIPLE OTHER PARTS OF THE STATEMENT

from SO_LINE a left join SO b on a.SO_ID = b.SO_ID where carrier_id = '2' and cast(a.so_id as varchar) + cast(a.line as varchar) = ?

Thanks for the help in advance!!!!!!!!
SteveThere isn't too much of a performance hit for doing this in the SELECT. That FROM will kill you though. I would make it:
SELECT
CAST(a.so_id AS VARCHAR) + CAST(a.line AS VARCHAR) AS cust_db_shipment_key,
blah,
blah,
blah
FROM
SO_LINE a
LEFT JOIN SO b ON a.SO_ID = b.SO_ID
WHERE
carrier_id = '2'
AND so_id = LEFT(@.whatever,2) --However the string is broken down.
AND a.line = RIGHT(@.whatever,2)|||OK...I have come to the conclusion that I will not be keeping the columns as int's. The (@.whatever, 2)...what is that representing?
Thanks,
Steve|||?? Whatever the "?" is in your post. I'm assuming your passing a string or something to this query aren't you?|||This works great so far. I am working off of a limited dataset so tomorrow will be the real test but so far so good.
Thanks again,
Steve

Help with UPDATE statement! TY!

Given the table (mytable)
my_id int (pk)
my_type char(1)
my_version tinyint
my_datetime datetime

Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 null
7 b 2 1/5/03
8 c 2 null
9 d 2 1/5/03
10 e 2 1/6/03

I want to write an update statement that will set all version 2
datetimes to their version 1 value when the version 2 value is null

After the update the data should look like:
Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 1/1/03
7 b 2 1/5/03
8 c 2 1/3/03
9 d 2 1/5/03
10 e 2 1/6/03

I've tried:

update my_table
set my_datetime = v1.my_datetime
from
(select *
from my_table
where my_version = 1 and my_datetime is not null) v1,
(select *
from my_table
where my_version = 2 and my_datetime is null) v2
where v1.my_type = v2.my_type

But this just updates all version 2 rows to the lowest date.

What am I doing wrong?

TIACan I assume that there is only one row where my_version=1 for each value of
my_type? If so:

UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<mdydnYICIoU7kxGiRVn-sQ@.giganews.com>...
> Can I assume that there is only one row where my_version=1 for each value of
> my_type? If so:
> UPDATE MyTable
> SET my_datetime =
> (SELECT my_datetime
> FROM MyTable AS M
> WHERE my_version = 1
> AND my_type = MyTable.my_type)
> WHERE my_datetime IS NULL

David, yes you can assume that. There are more versions than just 1 &
2 though so I modified your where statement to add:
UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL and my_version = 2

and it worked like a charm! THX!

Help with Update Query command Problem

Hi all,

I have this store procedure as follows:

Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)

as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email

where ProblemID = @.ProblemID

when I test the querry

exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'

what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.

Please help. I have set the ProblemID as the Primary key.

When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||

Find out the error. where ProblemID = @.ProblemID --> wrong

where @.ProblemID = ProblemID -- Right

cheers

|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?