1

I have two tables that hold information about a product

products
+-----------+------------+
|productid  |productname |
|Int        |varchar(50) |
+-----------+------------+


productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int      |Int       |Text        |Money |Int   |
|         |FK_From_  |            |      |      |
|         |productid_|            |      |      |
|         |products  |            |      |      |
+---------+----------+------------+------+------+

I am trying to update the stock levels using TSQL by passing the productName and stock to a stored procedure. I was hoping to be able to do something like this

Pseudo Code

UPDATE productdetail.stock
SET stock = @stock
WHERE productdetail.productid = products.productname

I "think" I need to use some sort of a join statement and have looked at this

and have tried to alter it to fit my code but have had no success.

Is what I am trying to do possible in one query? Or will I need to first select the productid, set it to a variable then use that as part of my update query, If it is possible to so it with a JOIN statement would it look something like this:

 UPDATE tab_productdetails 
 SET stock = @newStock 
 FROM tab_productdetails productid
 INNER JOIN
 tab_productdetails productid ON
 @productName = tab_products.productname

I am using SQL server 2012 express. If that makes any difference.

Community
  • 1
  • 1
Qu1nncunxIV
  • 163
  • 1
  • 2
  • 10

2 Answers2

2

You do not need JOIN to update a table. You need to use where and select

UPDATE productdetail
SET stock = @stock
WHERE productid = (select productid  from products where productname  = @productname )

Here @productname is the productname you are passing to the query.

1

You don't need a join (but you could do it that way). Clearest way is with a subquery:

UPDATE productdetail
 SET stock = @newStock 
WHERE productid = (SELECT productid FROM products WHERE product_name=@productname)
Rikalous
  • 4,514
  • 1
  • 40
  • 52