stored procedure to update data in multiple tables

saich

9y934SQL

Hi,

I am new to sql. I am practicing on foreign key relationships by doing a small application. It have 3 tables. One table named stpersonal with cloumns as stID, stname, stdateofbirth, stgender. stID column is identity column and primary key column. As the student contain multiple fields in education, i used second table named steducation with fields stID, stcollege, stgradyear.

stID in steducation table has foreign key relation with stID in stpersonal table.

And third table staddress for student addresses with columns stID, stAddress.

stID in staddress table has foreign key relation with stID in stpersonal table.

I got the stored procedure to get all student details and delete and insert. But I a unable to figure out stored procedure for update. I tried the following method to create stored procedure

`create procedure stupdate

(

@ID int,

@stname nvarchar(50),

@stdateofbirth nvarchar(50),

@stgender nvarchar(50),

@stcollege nvarchar(50),

@stgradyear datetime,

@stAddress nvarchar(50)

)

as begin

update stpersonal

set

stname = @stname,

stdateofbirth = @stdateofbirth,

stgender = @stgender,

stcollege = @stcollege,

stgradyear = @stgradyear,

stAddress = @stAddress

where stpersonal.stID = @ID

end ` This is the code I tried. I tried to use joins, but nothing worked out. So i hope I could overcome this.

Thanks in advance

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
Accepted answer
If you have different tables stpersonal, staddress, steducation then you need to update 3 tables individaully:

update stpersonal
set stname = @stname,
stdateofbirth = @stdateofbirth,
stgender = @stgender
where stpersonal.stID = @ID;

update steducation
set stcollege = @stcollege,
stgradyear = @stgradyear
where steducation.stID = @ID;

update staddress
set stAddress = @stAddress
where staddress.stID = @ID;
+2
Sourabh Choubey9y ago
note:If all the column in one table than this will be the structure.
create procedure updatetable
(
@ID int,
@stname nvarchar(50),
@stdateofbirth nvarchar(50),
@stgender nvarchar(50),
@stcollege nvarchar(50),
@stgradyear datetime,
@stAddress nvarchar(50)
)
as
begin
update tablename set stname=@stname
simillarly for all column till staddress
and finally use where condition
where id=@id
end

otherwise if all the column is from multiple table than u have to write multiple update statement.

If you have different tables stpersonal, staddress, steducation then you need to update 3 tables individaully:

update stpersonal
set stname = @stname,
stdateofbirth = @stdateofbirth,
stgender = @stgender
where stpersonal.stID = @ID;

update steducation
set stcollege = @stcollege,
stgradyear = @stgradyear
where steducation.stID = @ID;

update staddress
set stAddress = @stAddress
where staddress.stID = @ID;
+1