Subqueries and scalar expressions

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)

Subqueries and scalar expressions

Author

Topic

danteb
Starting Member

2 Posts

Posted - 2009-10-22 : 09:49:05

Hi!

I'm new here, and I have a question regarding the following query...

INSERT INTO TABLEA VALUES
('38A062302D4411D28E71006008960167','1111', 1, 8,
(select colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167'))

This returns the error below:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Could anyone suggest an alternate syntax that might work without having to use variables? :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-22 : 09:53:10

Try like this

INSERT INTO TABLEA

select '38A062302D4411D28E71006008960167','1111', 1, 8,(select colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

danteb
Starting Member

2 Posts

Posted - 2009-10-22 : 09:57:33

Thank you very much, this worked perfectly! :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 09:58:27

INSERT INTO TABLEA
select '38A062302D4411D28E71006008960167','1111', 1, 8,colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167'

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-22 : 09:59:24

or this:

EDIT:


INSERT INTO TABLEA
select '38A062302D4411D28E71006008960167'
       ,'1111'
       ,1
       ,8
       ,colA 
FROM   TABLEA 
WHERE  colB='01974B6D400D0175CCE6008CA3DE0DC1' 
AND    colC='38A062302D4411D28E71006008960167'

Be One with the Optimizer
TG

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-22 : 10:03:26

quote:
Originally posted by danteb

Thank you very much, this worked perfectly! :)

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 10:04:21

quote:
Originally posted by senthil_nagore

Try like this

INSERT INTO TABLEA

select '38A062302D4411D28E71006008960167','1111', 1, 8,(select colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This will give the same error if there is more than a value for the column colA

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-22 : 10:16:24

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Try like this

INSERT INTO TABLEA

select '38A062302D4411D28E71006008960167','1111', 1, 8,(select colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This will give the same error if there is more than a value for the column colA

Madhivanan

Failing to plan is Planning to fail

mmmmm..

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-22 : 10:30:00

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Try like this

INSERT INTO TABLEA

select '38A062302D4411D28E71006008960167','1111', 1, 8,(select colA FROM TABLEA WHERE
colB='01974B6D400D0175CCE6008CA3DE0DC1' AND colC='38A062302D4411D28E71006008960167')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This will give the same error if there is more than a value for the column colA

Madhivanan

Failing to plan is Planning to fail

Well, technically it will be a different error but an error none the less

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources