You are currently viewing the BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
When I run this code, it updates all the records in the table rather then only updating record with the ID of 1. Is this code suppose to update all records? Or just the record with primary key of 5?
My second question is, how do I update a record without using a primary key? Can I specify other attributes in a where clause rather then using a primary key?
Thanks everyone!
Novice
Last edited by NovicePGM2011; November 12th, 2011 at 05:54 PM.
You're absolutely right. I beleive that when the stored procedure script was pasted into the manuscript, the last line was omitted. My appologies. The code should read:
CREATE PROCEDURE spUpd_MyContacts
@ContactID int
, @FirstName nvarchar(50)
, @LastName nvarchar(50)
, @Phone nvarchar(25)
AS
UPDATE MyContacts
SET FirstName = @FirstName
, LastName = @LastName
, Phone = @Phone
WHERE ContactID = @ContactID
Regarding your second question, any column or combination of columns can be used in a WHERE clause to qualify records. Just keep in mind that only the primary key is used to guarantee uniqueness.
__________________ Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
You're absolutely right. I beleive that when the stored procedure script was pasted into the manuscript, the last line was omitted. My appologies. The code should read:
CREATE PROCEDURE spUpd_MyContacts
@ContactID int
, @FirstName nvarchar(50)
, @LastName nvarchar(50)
, @Phone nvarchar(25)
AS
UPDATE MyContacts
SET FirstName = @FirstName
, LastName = @LastName
, Phone = @Phone
WHERE ContactID = @ContactID
Regarding your second question, any column or combination of columns can be used in a WHERE clause to qualify records. Just keep in mind that only the primary key is used to guarantee uniqueness.
Thanks Paul!! I was about to give up!
This is all new to me.
So lets say using the above code, but instead of specifying the update by Id as you just mentioned, I wanted to use the same stored procedure but performed the update by using the stored procedure with a where clause.
Example: Update record is First name = paul and last name = Turley.
I know in the real world this would be the wrong way but I for the sake of learning.
If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:
create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName
__________________ Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:
create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName
Thanks again. In my Scenario, the First Name and Last names are primary Keys. This scenario is just an example.
In the real world, the name could be a company name, example: BestBuy.
Since we are not going to be changing the company name, we make the name the primary Key.
However, I want to update all other fields using one or multiple attributes.
Example: Where company name = 'BestBuy' and Company Location = 'MPLS'.
This way we can update the address phone numbers but not the name. This scenario is perfect if there is only one branch per city.
By the way, how would I called the example you mentioned above?
I don't want to change the name but instead is it as an attribute in the Where clause.
Thanks a Million!
Novice
Last edited by NovicePGM2011; November 12th, 2011 at 08:35 PM.
In this table I would like to use the Stored Procedure to
update all aspects of the tblStore using the storeName and the StoreBranch as a specifier in a where clause.
So if I have multiple stores I can only update one.
Code:
CREATE TABLE tblStore(
StoreName VARCHAR(30),
StoreBranch VARCHAR(30),
CONSTRAINT pktblStore PRIMARY KEY (StoreName, StoreBranch),
StoreAddress VARCHAR(30) NOT NULL,
StoreCity VARCHAR(30) NOT NULL,
StoreState CHAR(2) NOT NULL,
StoreZip CHAR(9) NOT NULL,
StorePhone CHAR(10) NOT NULL,
);
If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:
create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName
Here is what I did per your directions to update the table or a record without changing the name. Meaning I used the Store Name and the Store Branch in the where clause to update the table.
Updating a Record with Stored Procedure
Linear Mode
