We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Insert Statement with select expression in SQL Compact 3.5SP1

Medium Priority
447 Views
Last Modified: 2012-05-06
I am trying to execute the following on SQL Compact 3.5SP1, I get the error displayed...

insert groupmember(gmPKParent,gmPKChild)
values(
  (select svcPK from service where svcName = 'AOL_Messenger'),
  (select svcPK from service where svcName = 'AOL')
)

Major Error 0x80040E14, Minor Error 25501
> insert groupmember(gmPKParent,gmPKChild)
values(
  (select svcPK from service where svcName = 'AOL_Messenger'),
  (select svcPK from service where svcName = 'AOL')
)
There was an error parsing the query. [ Token line number = 3,Token line offset = 4,Token in error = select ]

Both select statements work and return 1 and 84 respectively.

svcPK is an int identity(1,1).

Thanks.

-Rowan
Comment
Watch Question

Commented:
Why don't you do it this way -

insert groupmember(gmPKParent,gmPKChild)
SELECT MAX(CASE WHEN svcName = 'AOL_MESSENGER' THEN svcPK ELSE 0 END)
            ,MAX(CASE WHEN svcName = 'AOL' THEN svcPK ELSE 0 END)
FROM service
WHERE svcName IN ('AOL_Messenger','AOL')

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That works, does the where clause at the bottom gain me anything?  svcName is unique.

Commented:
It just makes sure that the whole table does not have to be processed to execute the query.. Its just a prudent thing which will ensure quick execution with the least resources..

Author

Commented:
Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.