?
Solved

Insert Statement with select expression in SQL Compact 3.5SP1

Posted on 2009-02-12
4
Medium Priority
?
415 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
0
Comment
Question by:rowansmith
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23628272
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')
0
 
LVL 11

Author Comment

by:rowansmith
ID: 23628423
That works, does the where clause at the bottom gain me anything?  svcName is unique.
0
 
LVL 25

Expert Comment

by:reb73
ID: 23628502
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..
0
 
LVL 11

Author Comment

by:rowansmith
ID: 23628513
Thanks
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question