insert join????
-
hello all, does anybody out there know if it is possible to do a insert join... Well ok I know that it CAN be done but somewhere I have gotten off then beaten path.
I have a table (documents) and it has an ID field(GUID), then I have a document_image table with a documentID field, but as of right now it is null. I have a common field between the two tables called back_documentID (int). My where filter just narrows it down for certain manufacturers, the manufacturerCode is also in both tables. I believe the code would go something like:
insert
into document_image (documentID)
select
doc.id from [document] doc
inner
join document_image on document_image.back_documentid = doc.back_documentid
where
doc.manufacturerCode='GRO'
-----------------
I have tried this also:
update
document_image
set
documentID =
(
select doc.id from [document] doc
inner
join document_image on document_image.back_documentid = doc.back_documentid
where
doc.manufacturerCode='GRO')
-------
neither works, thanks for any suggestions.
Zach
-
Well, do you want to insert new rows into Document_Image or update existing rows in the table? This will tell you which type of statement you need to start with, an INSERT or UPDATE. Next, when you say "it doesn't work", what do you mean? Does SQL Server go up in smoke or do you just not get the results you thought you would? I'll just assume the latter so what happens? What would you like to happen?
-
Your examples indicates your trying to update the documentID column in the document_image table with the value of the ID column from the document table, using a join via back_documentid. Try this...
Update Document_Image
set
documentID = d.[ID]
from Document_Image di
inner join Document d on di.back_documentID = d.back_documentID
where d.manufacturerCode = 'GRO'
-
well, this is almost what I needed but this set the documentID for all manufacturers not just the ones with a manufacturerCode = 'GRO'
Is there any way to just set the document id's for just manufacturerCode = 'GRO'... I am trying and will let you all know if I solve it thanks again.
-
well i got it, thanks for the push in the right direction... here was what i had to have:
Update
Document_Image
set
documentID
= d.[ID]
from
Document_Image di
inner
join Document d on di.back_documentID = d.back_documentID
where
d.manufacturerCode = 'GRO' and di.manufacturerCode='GRO'
thanks for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply