February 23, 2009 at 6:59 pm
Hi,
I am having a problem with SQL likely because I'm still learning. I have two tables:
Table1 has two columns: ID and ImageID
1 : 56
2 : 75
3 : 45
4 : 55
5 : 75
Table2 has two columns: ImageID and Table1ID
I need to do a select distinct on Table1.ImageID so that I don't get any duplicates in Table2, but I also need Table2 to pick up both columns from Table1 so I can retain the original order by sorting by Table1ID.
I can do a select distinct to populate Table2 with the correct values in the ImageID field, but of course I don't have a way to select distinct on Table1.ImageID and also bring Table1.ID into Table2.
I think I would like to use some sort of UPDATE command to update Table2.Table1ID with the corresponding values from Table1.ID. A simple join will cause my duplicates to come back.
After populating Table2 I would like to loop through it doing an update on each row to get the values from Table1.ID into Table2.Table1ID.
Does this make sense and can someone please point me in the right direction?
Thanks,
Dave Williams
February 24, 2009 at 5:34 am
More information is needed to really understand what you really want...
You could go through the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for information on how to post better questions so that you get better responses...
From the data you provided ImageID 75 is associated with two IDs - 2 and 5. If you want to get this ImageID into Table2 and update the Table1ID column there with the ID value - which ID value do you want? i.e. 2 or 5 or both comma separated?
I've provided some sample code below which you can tweak to let us know what you expect:
DECLARE @Table1 TABLE(ID int, ImageID int)
DECLARE @Table2 TABLE(ImageID int, Table1ID int)
INSERT @Table1(ID,ImageID)
SELECT 1 , 56
UNION ALL
SELECT 2 , 75
UNION ALL
SELECT 3 , 45
UNION ALL
SELECT 4 , 55
UNION ALL
SELECT 5 , 75
INSERT @Table2(ImageID)
SELECT DISTINCT ImageId FROM @Table1
UPDATE @Table2 SET Table1ID = T1.ID FROM
@Table1 T1 INNER JOIN @Table2 T2 ON
T1.ImageId = T2.ImageId
SELECT * FROM @Table1
SELECT * FROM @Table2
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply