How to Update Records Following Relationship -Bandi [Resolved]

Posted by Jayakumars under Sql Server on 4/16/2015 | Points: 10 | Views : 335 | Status : [Member] [MVP] | Replies : 7
Hi
Bandi
Thanks for your reply. How to update following tables.



USE TEST
CREATE TABLE SUBJECTLIST
(
SubId int primary key identity(1,1),
SubName varchar(20)
)
Insert into SUBJECTLIST values('Tamil')
Insert into SUBJECTLIST values('English')
Insert into SUBJECTLIST values('Maths')
Insert into SUBJECTLIST values('Science')
Insert into SUBJECTLIST values('Social')
--SELECT * from SUBJECTLIST

CREATE TABLE SUBJECTASSIGN
(
SubAssId int Primary key identity(1,1),
SubId int,
SubAssgetdate datetime,
StudentId int
)


Insert into SUBJECTASSIGN values(1,GETDATE(),101)
Insert into SUBJECTASSIGN values(2,GETDATE()+1,102)
Insert into SUBJECTASSIGN values(1,GETDATE()+2,103)
Insert into SUBJECTASSIGN values(1,GETDATE()+1,104)
Insert into SUBJECTASSIGN values(3,GETDATE(),105)
Insert into SUBJECTASSIGN values(2,GETDATE(),108)
Insert into SUBJECTASSIGN values(4,GETDATE(),101)
Insert into SUBJECTASSIGN values(5,GETDATE()+3,101)
Insert into SUBJECTASSIGN values(6,GETDATE(),107)
Insert into SUBJECTASSIGN values(7,GETDATE(),102)


CREATE TABLE SUBJECTPROCESSASSIGN
(
SubProcId int Primary key identity(1,1),
SubId int,
StudentId int NULL
)

Insert into SUBJECTPROCESSASSIGN values(1,0)
Insert into SUBJECTPROCESSASSIGN values(2,0)
Insert into SUBJECTPROCESSASSIGN values(1,0)
Insert into SUBJECTPROCESSASSIGN values(3,0)
Insert into SUBJECTPROCESSASSIGN values(4,0)
Insert into SUBJECTPROCESSASSIGN values(1,0)
Insert into SUBJECTPROCESSASSIGN values(2,0)
Insert into SUBJECTPROCESSASSIGN values(3,0)
Insert into SUBJECTPROCESSASSIGN values(2,0)
Insert into SUBJECTPROCESSASSIGN values(4,0)
Insert into SUBJECTPROCESSASSIGN values(5,0)
Insert into SUBJECTPROCESSASSIGN values(5,0)
Insert into SUBJECTPROCESSASSIGN values(2,0)

--I have Update StudentId For this Table SUBJECTPROCESSASSIGN but i need joins this tables(SUBJECTLIST,SUBJECTASSIGN,SUBJECTPROCESSASSIGN)
-- for all table match this field only SubId

Mark as Answer if its helpful to you


Responses

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

Up
0
Down

Resolved

UPDATE SPA
SET SPA.StudentId=SA.StudentID
FROM SUBJECTLIST SL
JOIN SUBJECTASSIGN SA ON SL.SubID=Sa.SubId
JOIN SUBJECTPROCESSASSIGN SPA ON SPA.SubId = SA.SubId


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/17/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Can you explain the logic behind updating SUBJECTPROCESSASSIGN table?

From SUBJECTASSIGN table, you have 101, 103 & 104 students assigned to SubId=1; 102& 108 students are being assigned to SubID=2; and so on..

On what condition based you wanted to update SUBJECTPROCESSASSIGN table? or else post us back the expected output after 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/17/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

i need output this

SubProcId SubId StudentId
1 1 101
2 2 102
3 1 101
4 3 105
5 4 101
6 1 101
7 2 102
8 3 105
9 2 102
10 4 101
11 5 101
12 5 101
13 2 102

Mark as Answer if its helpful to you

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

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

Up
0
Down
Hi
Bandi

This is my Query

UPDATE SubjectProcessAssign SET StudentId=(SELECT StudentId from SubjectAssign A Inner join SubjectProcessAssign B On A.SubId=B.SubId)
Where SubProcId=2

But here i meet error this
Ambiguous column name 'StudentId'

Can you change and rectify them? can you change and update post here.

Mark as Answer if its helpful to you

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

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

Up
0
Down
UPDATE SubjectProcessAssign SET StudentId=(SELECT A.StudentId from SubjectAssign A Inner join SubjectProcessAssign B On A.SubId=B.SubId)
Where SubProcId=2



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/17/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

I tried your query like this


UPDATE SubjectProcessAssign SET StudentId=(SELECT A.StudentId from SubjectAssign A Inner join SubjectProcessAssign B On A.SubId=B.SubId)
Where SubProcId=2

I meet this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

after i put Group like this



UPDATE SubjectProcessAssign SET StudentId=(SELECT A.StudentId from SubjectAssign A Inner join SubjectProcessAssign B On A.SubId=B.SubId Group by StudentId)
Where SubProcId=2

Again coming same error

Ambiguous column name 'StudentId'.

how to solve this

Mark as Answer if its helpful to you

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

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

Up
0
Down
the error was due to the sub query is returning more than one StudentID. Even if you specify GROUP BY, it won't work, beacuse for each StudentId you have multiple SubIDs assigned...

So code as follows for updating only one record SubProcId=2 :
UPDATE SubjectProcessAssign SET StudentId=(SELECT A.StudentId from SubjectAssign A Inner join SubjectProcessAssign B On A.SubId=B.SubId Where SubProcId=2  )
Where SubProcId=2


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

Login to post response