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

Quote:

Originally Posted by 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.

update Student as s,AcademicQualification as a set s.FName='Maya',s.LName='Sharma',a.InstituteNm='St Joseph',a.University='Barkatullah' where s.RegNo==a.Regno

amitpatel66

Quote:

Originally Posted by ranjana1980

update Student as s,AcademicQualification as a set s.FName='Maya',s.LName='Sharma',a.InstituteNm='St Joseph',a.University='Barkatullah' where s.RegNo==a.Regno

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

ranjana1980

Quote:

Originally Posted by amitpatel66

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

I have an error where i using as clause

Delerna

where s.RegNo==a.Regno

Why the double equals?

should be
where s.RegNo=a.Regno

ranjana1980

Quote:

Originally Posted by Delerna

where s.RegNo==a.Regno

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

Quote:

Originally Posted by ranjana1980

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?..Please 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

Expand|Select|Wrap|Line Numbers

 
create proc UpdStudentAcademicQual @RegoNo as bigint,@FName as varchar(50),@LName as varchar(50),@InstituteNm as varchar(50),@University as varchar(50)

as

update Student

set FName=@FName,

    LName=@LName,

where RegNo=@RegoNo
 
update AcademicQualification

set InstituteNm=@InstituteNm,

    University=@University 

where Regno=@RegoNo

ranjana1980

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers

 
create proc UpdStudentAcademicQual @RegoNo as bigint,@FName as varchar(50),@LName as varchar(50),@InstituteNm as varchar(50),@University as varchar(50)

as

update Student

set FName=@FName,

    LName=@LName,

where RegNo=@RegoNo
 
update AcademicQualification

set InstituteNm=@InstituteNm,

    University=@University 

where Regno=@RegoNo

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

Quote:

Originally Posted by 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

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

Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

re: Update record of two table

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about