February 22, 2015 at 4:51 am
here i am having table proudct
CREATE TABLE Products
(
ProductID int identity(0,1),
ProductName varchar(255),
Stock int,
Active int
)
which is having data like this
Productid productname stock active
1 margo 20 1
2 ser 30 1
3 sera 50 1
4 vire 45 1
5 closeup 32 1
how i am just trying strore procedure to update all stock column which i need in a single query
productid stock
1 50
3 70
5 100
GO
CREATE PROCEDURE productUpdate
@ProductId nvarchar(50)=(1,3,5),
@Stock nvarchar(50) =(50,70,100)
AS
SET NOCOUNT ON;
UPDATE product
SET
Stock=@Stock
WHERE
ProductId=@ProductId;
February 22, 2015 at 6:03 am
You can only update multiple rows in a single statement if they have matching criteria. For example, there's a ShipByDate and they all have the same value, then the WHERE clause can be used to modify them. But, they can only be updated to a single value in a single statement.
In your example, you have three disparate rows with three disparate values. You will have to have three different update statements, one for each.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 22, 2015 at 11:49 pm
Create a new Table Type with Columns ProductId and Stock.
Pass this table type as parameter to the SP.
Then join your main table and this parameter table to update your main table.
____________________________________________________________
APViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply