a store procedure that is to do a simply update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

a store procedure that is to do a simply update

Author

Topic

yyujjiang@hotmail.com
Starting Member

5 Posts

Posted - 2009-04-02 : 17:54:13

I've got a test question, which is "Give me an example of a store procedure that is to do a simply update against the following tables."

two tables:
1. table name: product
primary key: id(int)
other fields: title(varchar(200))
description(varchar(500))
date_created(datetime)

2. table name product_price
primary key: id(int)
other fields: product_id(int)
size(int)
price(int)
location(int)

Could anyone here help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 17:57:10

We don't answer exam questions on SQL Team, you won't learn it if we did.

yyujjiang@hotmail.com
Starting Member

5 Posts

Posted - 2009-04-02 : 18:03:15

Sorry, what I am doing is preparing my exam, I wouldn't be able to get the real question before an exam

yyujjiang@hotmail.com
Starting Member

5 Posts

Posted - 2009-04-02 : 18:43:28

Can anyone please help me? This is not an exam question!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 18:53:57

Can you post what you've tried so far?

yyujjiang@hotmail.com
Starting Member

5 Posts

Posted - 2009-04-02 : 21:14:59

Below is the result I tried, do not know how to update the field's values in product_price table:

CREATE PROCEDURE update_product
@title varchar(200), @description varchar(500), @datte_created datetime
AS
UPDATE product_price
SET title=@title, description=@description, date_created=@date_created
FROM product_price INNER JOIN PRODUCT ON product_price.id = product_price.id

To robvolk: can you have a look please?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 22:33:35

I'll need more details on what you're trying to accomplish, but with what you have now:

The columns you're updating belong to one table, Product
Therefore the UPDATE needs to be corrected, and
Since you're only updating one table, you don't need the join

A larger problem is that the current statement will update all rows in the table. You need to include a parameter for the ID if this is not the intended behavior.

yyujjiang@hotmail.com
Starting Member

5 Posts

Posted - 2009-04-03 : 00:11:44

Thank you for your help, robvolk.

Not quite understand the test question. What I am trying to achieve is updating all of relative fields for a product in both tables. The product ID should be identified by user input.

According to your advice, I have modified my code:

CREATE PROCEDURE update_product
@ID int, @title varchar(200), @description varchar(500), @size int, @price int, @location int
AS
UPDATE product_price,product
SET title=@title, description=@description, size=@size, price=@price, location=@location
where product_price.product_id = product.id AND product.id=@ID

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-03 : 00:38:00

You cannot UPDATE two or more tables with a single UPDATE statement, you have to use one UPDATE per table. I'd suggest reading Books Online for the proper syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources