May 3, 2012 at 7:26 am
Hello Sirs/Mams,
A pleasant day to every one.
I have the following tables and a sample dummy data (we will assume that this is already stored):
--------------------------------------------------------
PERSON table:
person_id first_name last_name
1 JAMES HOWLETT
--------------------------------------------------------
STUDENT table:
person_id student_number
1 STUD-001
--------------------------------------------------------
ADDRESS table:
person_id address
1 British Columbia, Canada
--------------------------------------------------------
PHONE table:
person_id landline
1 123-45-67
The [font="Courier New"]person_id[/font] in [font="Courier New"]PERSON[/font] table is the primary key, and the [font="Courier New"]person_id[/font] in [font="Courier New"]STUDENT[/font], [font="Courier New"]ADDRESS[/font] and [font="Courier New"]PHONE[/font] are [font="Courier New"]foreign keys[/font] respectively.
I want to update/delete this record but instead of using [font="Courier New"]person_id[/font], my indication will be the [font="Courier New"]student_number[/font] from the [font="Courier New"]STUDENT[/font] table. I tried the following stored procedure but the query complains about something that is not update-able because the modification affects multiple base tables:
ALTER PROCEDURE [dbo].[SP_UPDATE_STUDENT_RECORD]
-- Add the parameters for the stored procedure here
@idstudent AS NVARCHAR(20),
@studfname AS NVARCHAR(50),
@studlname AS NVARCHAR(50),
@studaddress1 AS NVARCHAR(50),
@studphone1 AS NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
WITH STUDENT_RECORD AS (
SELECT s.STUDENT_NUMBER AS ID, p.FIRST_NAME, p.LAST_NAME, a.ADDRESS, c.LANDLINE
FROM PERSON AS p, STUDENT AS s, ADDRESS AS a, PHONE AS c
WHERE (p.PERSON_ID = s.PERSON_ID) AND (p.PERSON_ID = a.PERSON_ID) AND (p.PERSON_ID = c.PERSON_ID)
) UPDATE [STUDENT_RECORD] SET [FIRST_NAME] =@studfname, [MIDDLE_NAME] = @studmname, [LAST_NAME] = @studlname, [ADDRESS] = @studaddress1, [LANDLINE] =@studphone1
WHERE [ID] = @idstudent
END
I hope someone could help me revise my stored procedure.
But, if this query will be change into something that is update-able, will this be the same approach as deleting it (i.e. simply changing [font="Courier New"]UPDATE[/font] to [font="Courier New"]DELETE[/font])?.
Thank you and more power.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
May 3, 2012 at 7:37 am
You have a couple things going on here. First your query is using the old style join. While syntactically correct it is very hard to read these types of joins. What you did is created a cross join and then made it act like an inner join with the where clause. My terminology for this is an "equi-join". They can quite easily produce unexpected results because they are more difficult to read. I would suggest changing to something like this.
SELECT s.STUDENT_NUMBER AS ID, p.FIRST_NAME, p.LAST_NAME, a.ADDRESS, c.LANDLINE
FROM PERSON AS p
join STUDENT AS s on p.PERSON_ID = s.PERSON_ID
join ADDRESS AS a on p.PERSON_ID = a.PERSON_ID
join PHONE AS c on p.PERSON_ID = c.PERSON_ID
That makes is abundantly clear what your are doing. Also the table Address is a reserved word and it is highly advisable to avoid reserved words as object names.
---Now for your actual question---
The reason you can't do your update is because the update is trying to affect multiple tables. You can't do that. You have to update a single table at a time. Same with deletes. You can't delete from more than 1 table with a single statement.
You will have to modify this to update/delete each table individually.
Make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2012 at 7:46 am
I think you want something like :-
Update A
Set A.Address = @StudAddress1
From Student S
Join Address A
on S.PersonID = A.PersonID
Where S.Student_Number = @IDStudent
You'll need to adjust that statement for each table you want to update
Be a little careful though. Your database structure is such that a student could have more than address. This will update all the addresses for a given student.
edit> Sorry Sean, our posts crossed over.
BTW, that's a miss-use of the phrase equi-join. An equi-join is any join based on an equality. So:-
TableA
Join TableB
on TableA.FieldA = TableB.FieldB
is an equi-join
but:-
TableA
Join TableB
on TableA.FieldA > TableB.FieldB
or
TableA
Join TableB
on TableA.FieldA like TableB.FieldB + '%'
aren't equi-joins.
May 3, 2012 at 9:14 am
To Sean Lange:
Also the table Address is a reserved word and it is highly advisable to avoid reserved words as object names.
-No wonder why [font="Courier New"]ADDRESS[/font] becomes color blue in MSSQL Server Management Studio. Noted. Thanks a lot. 🙂
The reason you can't do your update is because the update is trying to affect multiple tables. You can't do that. You have to update a single table at a time. Same with deletes. You can't delete from more than 1 table with a single statement.
-Actually that is what I read already in other post that you cannot update/delete all tables with one single query, each table has it's own dedicated [font="Courier New"]UPDATE[/font] query and [font="Courier New"]DELETE[/font] query. I just cannot figured it out how to "communicate" to other table if I'll be using [font="Courier New"]student_number[/font] instead of [font="Courier New"]person_id[/font].
To FunkyDexter:
Using your suggestion, my update works now. 😀
And following Sean Lange, I have three updates, one update for [font="Courier New"]PERSON[/font], one update for ADDRESSES (table name was change thanks to Sean Lange), and one for [font="Courier New"]PHONE[/font].
You are all really awesome guys. Thanks.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
May 3, 2012 at 9:30 am
marksquall (5/3/2012)
-Actually that is what I read already in other post that you cannot update/delete all tables with one single query, each table has it's own dedicated [font="Courier New"]UPDATE[/font] query and [font="Courier New"]DELETE[/font] query. I just cannot figured it out how to "communicate" to other table if I'll be using [font="Courier New"]student_number[/font] instead of [font="Courier New"]person_id[/font].
Mark Squall
you can do an update like the following
UPDATE Addresses SET Address = blah
FROM Addresses a
INNER JOIN Student s
ON a.PersonID = s.PersonID
WHERE s.student_number = @student_number
you can pass a paramater or hard code the student_number from the students table while actually updating the address for the person id associated with the student id.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 10:04 am
marksquall (5/3/2012)
Actually that is what I read already in other post that you cannot update/delete all tables with one single query, each table has it's own dedicated [font="Courier New"]UPDATE[/font] query and [font="Courier New"]DELETE[/font] query. I just cannot figured it out how to "communicate" to other table if I'll be using [font="Courier New"]student_number[/font] instead of [font="Courier New"]person_id[/font].
Since you're updating multiple tables, I think the best approach is to use the student number to look up the person ID, which you then use to update the other tables. Something like the following.
DECLARE @person_ID INT
SELECT @person_ID = Person_ID
FROM Students
WHERE Student_Number = @student_number
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 4, 2012 at 6:28 am
To J. Drew Allen, capn.hector:
Thank you. Actually it is just a sudden idea that I will just transfer the value of [font="Courier New"]person_id[/font] (in a variable) from [font="Courier New"]PERSON[/font] table then use it my [font="Courier New"]UPDATE[/font]'s [font="Courier New"]WHERE[/font] clause in all of my table, I just do not know how to do that. 🙁
But thanks to you...now I do. 😎
Thanks a lot everyone. I owe you all a lot. 😀
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply