How to Update Records Following Relationship -Bandi [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

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

0	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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

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

0	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

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

Latest Posts