How to Update Following Data - Bandi [Resolved]

Posted by Jayakumars under Sql Server on 4/7/2015 | Points: 10 | Views : 489 | Status : [Member] [MVP] | Replies : 3
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


Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

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

Login to post response