asked on

How do I rewrite this query

-- insert all checklist items from DocHub_Dev.Checklist
insert folder(title, parentid, sort, canupload, dochub_checklistID, dochub_parentID, projectid, dochub_projectid)
select name,
NULL,
c.sort,
abs(container - 1),
checklistid,
parent,
(select id from project p where p.dochub_collectionid = c.collectionid), --Returning more than 1 value.
collectionid
from vbsql02.dochub_dev.dbo.checklist c

onlyaymie

Before choosing an approach, I would spotcheck a few of the multiple rows that are coming back to make sure there isn't some problem with arbitrarily choosing an id of dups.

to use the newest Id:

insert folder(title, parentid, sort, canupload, dochub_checklistID, dochub_parentID, projectid, dochub_projectid)
select name,
NULL,
c.sort,
abs(container - 1),
checklistid,
parent,
(select max(id) from project p where p.dochub_collectionid = c.collectionid), --Returning more than 1 value.
collectionid
from vbsql02.dochub_dev.dbo.checklist c

to use the oldest id:

insert folder(title, parentid, sort, canupload, dochub_checklistID, dochub_parentID, projectid, dochub_projectid)
select name,
NULL,
c.sort,
abs(container - 1),
checklistid,
parent,
(select min(id) from project p where p.dochub_collectionid = c.collectionid), --Returning more than 1 value.
collectionid
from vbsql02.dochub_dev.dbo.checklist c

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial