Click here to Skip to main content
13,664,006 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
i have two table:
1.Folder(FOlderID,DefaultPhotoID)
2.Gallery(PhotoID,FolderID)

and data in my table shows look like:

Folder Table:-
FolderID--DefaultPhotoID
1--NULL
2--NULL

Gallery Table:-
PhotoID--FolderID
101--1
102--1
103--1
104--2
105--2
106--2

now i need to update DefaultPhotoID column of folder table by first PhotoID of Gallery table.It means after update folder table data look like:

FolderID--DefaultPhotoID
1--101
2--104

What I have tried:

i am trying it using join on both table but its not working..
Posted 12-Jan-18 19:52pm
Updated 12-Jan-18 20:41pm
Comments
Bryian Tan 13-Jan-18 2:12am
   
Where is your query?
TCS54321 13-Jan-18 2:16am
   
update folder set Folder.DefaultPhotoID=
(SELECT Gallery.PhotoID
FROM Folder INNER JOIN
Gallery ON Folder.FolderID = Gallery.FolderID)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try this:
UPDATE Folder  
SET DefaultPhotoID = FirstPhoto
FROM (SELECT FolderID AS FID, MIN(PhotoID) AS FirstPhoto
      FROM Gallery
      GROUP BY FolderID) AS g 
WHERE FolderID = FID
  Permalink  
Comments
TCS54321 13-Jan-18 2:22am
   
tnx sir.. its working fine for me..
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Here is an example. The query first need to identity the first PhotoId for each folderId. Then do the update by folderId.

DECLARE @Folder TABLE (FolderId INT, DefaultPhotoId INT NULL)
DECLARE @Gallery TABLE (PhotoId INT, FolderId INT)

INSERT INTO @Folder
	SELECT 1, NULL UNION
	SELECT 2, NULL

INSERT INTO @Gallery
	SELECT 101, 1 UNION
	SELECT 102, 1 UNION
	SELECT 103, 1 UNION
	SELECT 104, 2 UNION
	SELECT 105, 2 UNION
	SELECT 106, 2 

;WITH temp AS (
	SELECT f.FolderId, g.PhotoId, ROW_NUMBER() OVER(PARTITION BY f.FolderId ORDER BY PhotoId) AS RowNumber 
	FROM @Folder f JOIN @Gallery g ON f.FolderId = g.FolderId
) -- SELECT * FROM temp WHERE RowNumber = 1
UPDATE f 
	SET f.DefaultPhotoId = t.PhotoId
FROM @Folder f 
	JOIn temp t ON f.FolderId = t.FolderId

SELECT * FROM @Folder


Output:
FolderId	DefaultPhotoId
1	        101
2	        104
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 13 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100