sql query for table update

Question

0.00/5 (No votes)

See more:

I have a table 'Dependants' having column FamilyID,MemberID,MemberName,gender and age

FamilyID    MemberID  MemberName     Gender   Age
001                    dharm            M
002                    mukesh           M
002                     sagr            M
002                    sfsfsf           M
003                    dfdff
003                    dfdfgd           F
004                     dffff           F
004                     safaf           F
004                     sdafsa          F

Now, i want, if there are two or more row of a same familyid, then the memberid should be 1 and 2 and3 for the same familyid.
After that table should be look like this:

FamilyID    MemberID  MemberName     Gender   Age
001           1          dharm            M
002           1         mukesh           M
002           2          sagr            M
002           3         sfsfsf           M
003           1         dfdff
003           2         dfdfgd           F
004           1         dffff            F
004           2          safaf           F
004           3          sdafsa          F

I have this table in the sql

Pls, give me the query for this problem

Posted 6-Jun-13 21:11pm

dharmkumar

Updated 6-Jun-13 21:20pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 7-Jun-13 3:21am

Do not SHOUT!

2 solutions

Solution 1

Hi,

Check the following code....

SQL

UPDATE D SET D.MemberID=T.MemberID
FROM Dependants D
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY FamilyID ORDER BY MemberName) 'MemberID', FamilyID, MemberName
            FROM Dependants) T ON T.FamilyID=D.FamilyID AND T.MemberName=D.MemberName

Regards,
GVPrabu

Posted 6-Jun-13 21:34pm

gvprabu

Updated 6-Jun-13 21:52pm

v2

Comments

Maciej Los 7-Jun-13 3:54am

The same answer ;)
+5!

gvprabu 7-Jun-13 3:59am

yes, SQL developers will think almost same like ours :-)

Maciej Los 7-Jun-13 4:03am

By the way: you're quick as a F-16[^] ;)

gvprabu 7-Jun-13 4:05am

hi... this it too tooo much... I am a small Kid. ok

gvprabu 7-Jun-13 4:01am

hi friend.... share your mail ID. Mine is [removed]mail[/removed]

Maciej Los 7-Jun-13 4:04am

I removed your email from comment, because of spamm robots. I'll send you a message ;)

gvprabu 7-Jun-13 4:05am

ok fine, No Probs. If possible pls add my mail ID to Gtak

gvprabu 7-Jun-13 4:03am

Your points : 59,600
My Points : 5,906
See almost Same, One small Difference. only one Zero (0) is less
:-) :-) :-)

Maciej Los 7-Jun-13 4:05am

;)

dharmkumar 7-Jun-13 4:26am

Thanks GPParbu it did work

gvprabu 7-Jun-13 5:24am

Always welcomee

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)

Maciej Los · Accepted Answer · 2013-06-06T21:35:00

Try this:

SQL

DECLARE @fam TABLE (FamilyID VARCHAR(3), MemberID INT NULL, MemberName VARCHAR(30), Gender VARCHAR(2) NULL, Age INT NULL)

INSERT INTO @fam (FamilyID, MemberID, MemberName, Gender, Age)
SELECT '001' AS FamilyID, NULL AS MemberId, 'dharm' AS MemberName, 'M' AS Gender, NULL AS Age
UNION ALL SELECT '002', NULL, 'mukesh', 'M', NULL
UNION ALL SELECT '002', NULL, 'sagr', 'M', NULL
UNION ALL SELECT '002', NULL, 'sfsfsf', 'M', NULL
UNION ALL SELECT '003', NULL, 'dfdff', NULL, NULL
UNION ALL SELECT '003', NULL, 'dfdfgd', 'F', NULL
UNION ALL SELECT '004', NULL, 'dffff', 'F', NULL
UNION ALL SELECT '004', NULL, 'safaf', 'F', NULL
UNION ALL SELECT '004', NULL, 'sdafsa', 'F', NULL

UPDATE t1 SET t1.MemberID = t2.MemberId
FROM @fam AS t1 INNER JOIN (
		SELECT FamilyID, ROW_NUMBER() OVER(PARTITION BY FamilyID ORDER BY MemberName) AS MemberID, MemberName
		FROM @fam
	) AS t2 ON t1.FamilyID = t2.FamilyId AND t1.MemberName = t2.MemberName  

SELECT *
FROM @fam