please correct my query - 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

Other SQL Server Topics (2005)

please correct my query

Author

Topic

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-29 : 11:03:32

DECLARE @fullname nvarchar(50)
SET @fullname =
(SELECT (OriginalName + ContentType) AS Name
FROM Files_Dyn)
INSERT
INTO Files_Dyn(FullName)
VALUES (@fullname) where username = 'user_admin'
...what is the wrong with this query..it is giving 'Incorrect syntax near the keyword 'where'. Please correct me!
thanks in advance!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-29 : 12:21:52

You cannot do an INSERT with WHERE. did you mean to UPDATE the row?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-29 : 13:22:29

DECLARE @fullname nvarchar(50)
SET @fullname =
(SELECT (OriginalName + ContentType)
FROM Files_Dyn)
UPDATE Files_Dyn
SET FullName = @fullname, Department = 'Admin'
WHERE username = ' user_admin '

....now it is saying 'Subquery returned more than one value. This is not permitted when the subquery follows =,!=, <=, >= or when subquery is used as an expression'.......please correct me..thanks dinakar for your reply.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-29 : 15:29:26

The error is here:


SET @fullname =
(SELECT (OriginalName + ContentType)
FROM Files_Dyn)

Your SELECT query is returning more than one value and you are trying to assign the result to one varibale, which is of course not possible and thus SQL Server is complaining about it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-29 : 19:55:43

What are you trying to do?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshprabhu
Starting Member

32 Posts

Posted - 2007-09-30 : 02:12:32

I want to concatenate the values of OriginalName and ContentType, and I want to set the result to fullname parameter which i want to insert into FullName column....How can I do this? Please help me!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-30 : 02:58:13

[code]
insert into target_table(fullname, . . . . .)
select OriginalName + ContentType, . . . .
from Files_Dyn
where . . .
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shaihan
Starting Member

20 Posts

Posted - 2007-09-30 : 10:47:18

the problem is ur select query is returning multiple row as DINAKAR has already figured out. you cannot insert multiple row in a single row. make sure when you are selecting the values it returns one and only one row. you must put a where clause to make it happen.

select (OriginalName + ContentType) Name
from Files_Dyn
where <your condtion goes here>

S ]-[ /-\ | ]-[ /-\ N

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-30 : 13:17:17

sounds like you sut want

update Files_Dyn
set FullName = OriginalName + ContentType

maybe
update Files_Dyn
set FullName = OriginalName + ContentType
where username = 'user_admin'

if you only want to update that username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshprabhu
Starting Member

32 Posts

Posted - 2007-10-01 : 11:06:23

Thanks nr, its worked out. Thanks alot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 02:59:18

also you need to handle NULLs

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources