How to Update Following Data - Bandi [Resolved]

Posted by Jayakumars under Sql Server on 4/7/2015 | Points: 10 | Views : 1206 | Status : [Member] [MVP] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Create Table StudNameList
(
StudId int,
StudName varchar(50),
ClassId int
)

INSERT INTO StudNameList Values(101,'John',1)
INSERT INTO StudNameList Values(105,'John Wesly',1)
INSERT INTO StudNameList Values(115,'John Alferd',2)
INSERT INTO StudNameList Values(115,'Rufans',3)
INSERT INTO StudNameList Values(117,'Rufas',4)

Create Table StudCmpList
(
StudentID int NULL,
DeptName varchar(50),
ClassId int
)

INSERT INTO StudCmpList Values(NULL,'IT',1)
INSERT INTO StudCmpList Values(NULL,'SCIENCS',2)
INSERT INTO StudCmpList Values(NULL,'MATHS',1)
INSERT INTO StudCmpList Values(NULL,'SCIENCE',4)
INSERT INTO StudCmpList Values(NULL,'SSCIENCE',3)

SELECT * from StudNameList
SELECT * from StudCmpList

i need update for StudCmpList table for this StudentID which have studentid and classid matches records update need how
will do this.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 4/9/2015 [Member] [MVP] Platinum | Points: 50

0	Create Table StudNameList ( StudId int, StudName varchar(50), ClassId int ) INSERT INTO StudNameList Values(101,'John',1) INSERT INTO StudNameList Values(105,'John Wesly',1) INSERT INTO StudNameList Values(115,'John Alferd',2) INSERT INTO StudNameList Values(116,'Rufans',3) INSERT INTO StudNameList Values(117,'Rufas',4) Create Table StudCmpList ( StudentID int NULL, DeptName varchar(50), ClassId int ) INSERT INTO StudCmpList Values(NULL,'IT',1) INSERT INTO StudCmpList Values(NULL,'SCIENCS',2) INSERT INTO StudCmpList Values(NULL,'MATHS',1) INSERT INTO StudCmpList Values(NULL,'SCIENCE',4) INSERT INTO StudCmpList Values(NULL,'SSCIENCE',3) /* --I need Output this StudentId DeptName ClassId 101 IT 1 115 SCIENCE 2 105 MATHS 1 117 SCIENCE 4 116 SSCIENCE 3 / -- UPDATE statement to get above result UPDATE SC SET Sc.StudentID=SN.StudId FROM (SELECT ROW_NUMBER() OVER( PARTITION BY ClassId ORDER BY ClassID) Classes, FROM StudCmpList ) SC JOIN (SELECT ROW_NUMBER() OVER( PARTITION BY ClassId ORDER BY StudId) StudentIDs,* FROM StudNameList) SN ON SN.StudentIDs= Sc.Classes and SN.ClassId=SC.ClassID --Verify the output SELECT * FROM StudCmpList --Clean up code --DROP TABLE StudCmpList --DROP TABLE StudNameList Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Create Table StudNameList 
( 
StudId int, 
StudName varchar(50), 
ClassId int 
) 


INSERT INTO StudNameList Values(101,'John',1) 
INSERT INTO StudNameList Values(105,'John Wesly',1) 
INSERT INTO StudNameList Values(115,'John Alferd',2) 
INSERT INTO StudNameList Values(116,'Rufans',3) 
INSERT INTO StudNameList Values(117,'Rufas',4) 


Create Table StudCmpList 
( 
StudentID int NULL, 
DeptName varchar(50), 
ClassId int 
) 

INSERT INTO StudCmpList Values(NULL,'IT',1) 
INSERT INTO StudCmpList Values(NULL,'SCIENCS',2) 
INSERT INTO StudCmpList Values(NULL,'MATHS',1) 
INSERT INTO StudCmpList Values(NULL,'SCIENCE',4) 
INSERT INTO StudCmpList Values(NULL,'SSCIENCE',3) 

/*
--I need Output this

StudentId	DeptName ClassId
101	IT	1
115	SCIENCE	2
105	MATHS	1
117	SCIENCE	4
116	SSCIENCE	3
*/
-- UPDATE statement to get above result
UPDATE SC
SET Sc.StudentID=SN.StudId 
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY ClassId ORDER BY ClassID) Classes, * FROM StudCmpList ) SC
JOIN (SELECT  ROW_NUMBER() OVER( PARTITION BY ClassId ORDER BY StudId) StudentIDs,* FROM StudNameList) SN
ON SN.StudentIDs= Sc.Classes and SN.ClassId=SC.ClassID
 
--Verify the output
SELECT * FROM StudCmpList

--Clean up code 
--DROP TABLE StudCmpList
--DROP TABLE StudNameList

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/8/2015 [Member] [MVP] Platinum | Points: 25

0	Sample data is not clear in this example query... As per my understanding, you need to update StudentID of table StudCmpList by matching ClassID of both StudNameList & StudCmpList... Here, there are two students( 101,105) under ClassID=1. In this case, what should be your StudentId for StudCmpList table? Post me back the expected output of StudCmpList table after the UPDATE .... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Sample data is not clear in this example query...

As per my understanding, you need to update StudentID of table StudCmpList by matching ClassID of both StudNameList & StudCmpList...

Here, there are two students( 101,105) under ClassID=1. In this case, what should be your StudentId for StudCmpList table?

Post me back the expected output of StudCmpList table after the UPDATE ....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/8/2015 [Member] [MVP] Bronze | Points: 25

0	Create Table StudNameList ( StudId int, StudName varchar(50), ClassId int ) INSERT INTO StudNameList Values(101,'John',1) INSERT INTO StudNameList Values(105,'John Wesly',1) INSERT INTO StudNameList Values(115,'John Alferd',2) INSERT INTO StudNameList Values(116,'Rufans',3) INSERT INTO StudNameList Values(117,'Rufas',4) Create Table StudCmpList ( StudentID int NULL, DeptName varchar(50), ClassId int ) INSERT INTO StudCmpList Values(NULL,'IT',1) INSERT INTO StudCmpList Values(NULL,'SCIENCS',2) INSERT INTO StudCmpList Values(NULL,'MATHS',1) INSERT INTO StudCmpList Values(NULL,'SCIENCE',4) INSERT INTO StudCmpList Values(NULL,'SSCIENCE',3) --I need Output this StudentId DeptName ClassId 101 IT 1 115 SCIENCE 2 105 MATHS 1 117 SCIENCE 4 116 SSCIENCE 3 Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Create Table StudNameList
(
StudId int,
StudName varchar(50),
ClassId int
)

INSERT INTO StudNameList Values(101,'John',1)
INSERT INTO StudNameList Values(105,'John Wesly',1)
INSERT INTO StudNameList Values(115,'John Alferd',2)
INSERT INTO StudNameList Values(116,'Rufans',3)
INSERT INTO StudNameList Values(117,'Rufas',4)

Create Table StudCmpList
(
StudentID int NULL,
DeptName varchar(50),
ClassId int
)

INSERT INTO StudCmpList Values(NULL,'IT',1)
INSERT INTO StudCmpList Values(NULL,'SCIENCS',2)
INSERT INTO StudCmpList Values(NULL,'MATHS',1)
INSERT INTO StudCmpList Values(NULL,'SCIENCE',4)
INSERT INTO StudCmpList Values(NULL,'SSCIENCE',3)

--I need Output this

StudentId DeptName ClassId
101 IT 1
115 SCIENCE 2
105 MATHS 1
117 SCIENCE 4
116 SSCIENCE 3

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts