September 3, 2009 at 1:49 pm
Is this possible??? to have a insert statement that uses a select statement that gets data from the inserting table and have a condition that a field from the select match the feild value in the inserting field..
I hope that makes sense?
September 3, 2009 at 1:57 pm
Show us what you are asking with a concrete example. Just from your description, I'm not sure what you are asking.
September 3, 2009 at 2:07 pm
No clue if this is what you are asking for but it was the first thing that came to my mind.
CREATE TABLE #temp (test INT, test2 INT, data VARCHAR(10))
INSERT INTO #temp
SELECT 1, 2, 'a'
UNION ALL
SELECT 2, 1, 'b'
UNION ALL
SELECT 3, 4, 'c'
SELECT * FROM #temp T
INSERT INTO #temp
SELECT a.* FROM #temp a
INNER JOIN #temp b ON a.test = b.test2
SELECT * FROM #temp T
DROP TABLE #temp
September 3, 2009 at 2:09 pm
ya know what, i figured it out,, i was meaning a update not an insert,, my bad,, thanks though 🙂
September 3, 2009 at 2:40 pm
This is what i am trying to accomplish, i dont think it will work,, but i think the theory of what i am accomplish is here..
this is what i am trying to accomplish
UPDATE A TABLE
update [ROIMaster].[dbo].[certiProdComps] rc
SET FIELDS IN TABLE I WANT TO UPDATE
set ([cpmpProdID]
,[cpmpProd]
) =
WITH VALUES FROM A VIEW
(SELECT DISTINCT top 1 IM.Novo_IM,IM.Item_Part_Nbr
FROM certiProdComps INNER JOIN
[Prolab-Select All Compounds] ON certiProdComps.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID INNER JOIN
IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr
)
THEN ONLY UPDATE THE RECORDS FOR EACH ROW IN THE TABLE IM UPDATING THAT HAS THE SAME ID AS A VALUE RETURNED FROM THE VIEW RETURN
where exists rc.cpmpProdID = (SELECT DISTINCT top 1 certiProdComps.cpmpID
FROM certiProdComps INNER JOIN
[Prolab-Select All Compounds] ON certiProdComps.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID INNER JOIN
IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr
)
September 3, 2009 at 2:47 pm
jerseyeddiem (9/3/2009)
This is what i am trying to accomplish, i dont think it will work,, but i think the theory of what i am accomplish is here..this is what i am trying to accomplish
UPDATE A TABLE
update [ROIMaster].[dbo].[certiProdComps] rc
SET FIELDS IN TABLE I WANT TO UPDATE
set ([cpmpProdID]
,[cpmpProd]
) =
WITH VALUES FROM A VIEW
(SELECT DISTINCT top 1 IM.Novo_IM,IM.Item_Part_Nbr
FROM certiProdComps INNER JOIN
[Prolab-Select All Compounds] ON certiProdComps.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID INNER JOIN
IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr
)
THEN ONLY UPDATE THE RECORDS FOR EACH ROW IN THE TABLE IM UPDATING THAT HAS THE SAME ID AS A VALUE RETURNED FROM THE VIEW RETURN
where exists rc.cpmpProdID = (SELECT DISTINCT top 1 certiProdComps.cpmpID
FROM certiProdComps INNER JOIN
[Prolab-Select All Compounds] ON certiProdComps.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID INNER JOIN
IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr
)
I am short on time but the syntax I think you are looking for is like
update rc
set [cpmpProdID] = IM.Novo_IM --i think this is what you want here
,[cpmpProd] = IM.Item_Part_Nbr --i think this is what you want here
from [ROIMaster].[dbo].[certiProdComps] rc
INNER JOIN [Prolab-Select All Compounds] ON rc.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID
INNER JOIN IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr
where exists rc.cpmpProdID = (SELECT DISTINCT top 1 certiProdComps.cpmpID
FROM certiProdComps INNER JOIN
[Prolab-Select All Compounds] ON certiProdComps.cpmpProlabCompId = [Prolab-Select All Compounds].PRODUCT_ID INNER JOIN
IM ON [Prolab-Select All Compounds].CODE = IM.Item_Part_Nbr)
I hope this will get you closer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply