Need help in SQL query.

Question

0.00/5 (No votes)

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

TCS54321

Updated 12-Jan-18 20:41pm

Add a Solution

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)

2 solutions

Solution 2

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

SQL

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

Posted 12-Jan-18 20:20pm

Bryian Tan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2018-01-12T20:18:00

Solution 1

Try this:

SQL

UPDATE Folder  
SET DefaultPhotoID = FirstPhoto
FROM (SELECT FolderID AS FID, MIN(PhotoID) AS FirstPhoto
      FROM Gallery
      GROUP BY FolderID) AS g 
WHERE FolderID = FID

Posted 12-Jan-18 20:18pm

OriginalGriff

Comments

TCS54321 13-Jan-18 2:22am

tnx sir.. its working fine for me..