Update record of two table

[ranjana1980]

How to update record of two tables when in Table A RegNo is primary key
and in Table B RegNo is foreign Key and i like to update where A.RegNo=B.RegNo

[Delerna]

This is a bit too vague for me to determine what code to post from hundreds of possibilities

Can you post some sample data for the two tables as well as an example of what it is you want to update.

[ranjana1980]

This is a bit too vague for me to determine what code to post from hundreds of possibilities

Can you post some sample data for the two tables as well as an example of what it is you want to update.

update Student as s,AcademicQuali fication as a set s.FName='Maya', s.LName='Sharma ',a.InstituteNm ='St Joseph',a.Unive rsity='Barkatul lah' where s.RegNo==a.Regn o

[amitpatel66]

update Student as s,AcademicQuali fication as a set s.FName='Maya', s.LName='Sharma ',a.InstituteNm ='St Joseph',a.Unive rsity='Barkatul lah' where s.RegNo==a.Regn o

And what did you get after running the above query??

[ranjana1980]

And what did you get after running the above query??

I have an error where i using as clause

[Delerna]

where s.RegNo==a.Regn o


Why the double equals?

should be
where s.RegNo=a.Regno

[ranjana1980]

where s.RegNo==a.Regn o


Why the double equals?

should be
where s.RegNo=a.Regno

Its my mistake i wrote double equal actually I will try with single equal to and i face same problem

[amitpatel66]

Its my mistake i wrote double equal actually I will try with single equal to and i face same problem

Do you still face any problem?..Pleas e post the error that your query displays for our reference?

[Delerna]

A number of problems
1) you cannot give the table you are updating an alias (as far as I know).
2) You can't give update a list of tables to update like that. You just get the error "Line1: syntax error near ,"
3) Even if that query did work it would update every record in both tables to the same values. Or is what you want to do?


I suggest that you simplify it and use two seperate update queries in a stored proc or udf
[code=sql]
create proc UpdStudentAcade micQual @RegoNo as bigint,@FName as varchar(50),@LN ame as varchar(50),@In stituteNm as varchar(50),@Un iversity as varchar(50)
as
update Student
set FName=@FName,
LName=@LName,
where RegNo=@RegoNo

update AcademicQualifi cation
set InstituteNm=@In stituteNm,
University=@Uni versity
where Regno=@RegoNo
[/code]

[ranjana1980]

A number of problems
1) you cannot give the table you are updating an alias (as far as I know).
2) You can't give update a list of tables to update like that. You just get the error "Line1: syntax error near ,"
3) Even if that query did work it would update every record in both tables to the same values. Or is what you want to do?


I suggest that you simplify it and use two seperate update queries in a stored proc or udf
[code=sql]
create proc UpdStudentAcade micQual @RegoNo as bigint,@FName as varchar(50),@LN ame as varchar(50),@In stituteNm as varchar(50),@Un iversity as varchar(50)
as
update Student
set FName=@FName,
LName=@LName,
where RegNo=@RegoNo

update AcademicQualifi cation
set InstituteNm=@In stituteNm,
University=@Uni versity
where Regno=@RegoNo
[/code]

Hi,
Thanks For this Solution.Your Previous three point I have clear regarding error.
I like to update different values of both the tables having same regno of both tables.
Can I Solve this using trigger but I don't know how to update two tables with different values with having same RegNo using trigger

[Delerna]

Not sure I understand your question.

A trigger is a stored proc that is called when a particular event occurs on a table.
So you should be able to do something similar to the solution I gave you.
That solution updates two different tables to different values for the same RegoNo. Thats why Im unsure about your question

You could also put the solution I gave into a UDF

[ranjana1980]

Not sure I understand your question.

A trigger is a stored proc that is called when a particular event occurs on a table.
So you should be able to do something similar to the solution I gave you.
That solution updates two different tables to different values for the same RegoNo. Thats why Im unsure about your question

You could also put the solution I gave into a UDF

I use your stored procedure code to update two tables but i get error in where clause

[Delerna]

what is the error that you get