用于表更新的sql查询 [英] sql query for table update

问题描述

我有一张表'Dependents'，其列有FamilyID，MemberID，MemberName，性别和年龄

 FamilyID会员ID会员姓名性别年龄
 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 

现在，我想，如果有两行或更多行familyid，那么memberid应该是1和2和3为同一个familyid。

之后该表应该是这样的：



< pre lang =text> FamilyID会员ID会员姓名性别年龄
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

我在sql中有这个表



请给我查询此问题

解决方案

您好，



请检查以下代码....

 更新 D  SET  D.MemberID = T .MemberID 
  FROM 家属D 
  INNER   JOIN （ SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  FamilyID  ORDER   BY  MemberName）'  MemberID'，FamilyID，MemberName 
  FROM 依赖项）T  ON  T.FamilyID = D.FamilyID  AND  T.MemberName = D.MemberName 

问候，

GVPrabu

试试这个：

  DECLARE   @ fam   TABLE （FamilyID  VARCHAR （ 3 ），MemberID  INT   NULL ，MemberName  VARCHAR （ 30 ），性别 VARCHAR （ 2 ） NULL ，年龄 INT   NULL ）
 
  INSERT   INTO   @ fam （FamilyID，MemberID，MemberName ，性别，年龄）
  SELECT  '  001 '  AS  FamilyID， NULL   AS  MemberId，'  dharm'  AS  MemberName，'  M'  AS 性别，  NULL   AS 年龄
  UNION   ALL   SELECT  '  002'， NULL ，'  mukesh'，'  M'，< span class =code-keyword> 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  所有  SELECT  '  003'， NULL ，'  dfdfgd'，'  F'， NULL  
  UNION   AL L   SELECT  '  004'， NULL ，'  dffff'， '  F'， NULL  
  UNION   ALL   SELECT  '  004'， NULL ，'  safaf'，'  F'， NULL  
  UNION   ALL   SELECT  '  004'， NULL ，'  sdafsa'，'  F'， NULL  
 
 更新 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  t 2  ON  t1.FamilyID = t2.FamilyId  AND  t1.MemberName = t2.MemberName 
 
  SELECT  * 
  FROM   @ fam  

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

解决方案

Hi,

Check the following code....

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

Try this:

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

这篇关于用于表更新的sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！