Using JOIN to UPDATE table

Question

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.

user2282410 · Accepted Answer · 2013-07-08 08:57:45Z

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.

answered Jul 8, 2013 at 8:57

user2282410

Sign up to request clarification or add additional context in comments.

1 Comment

Qu1nncunxIV Over a year ago

Cheers for that. Thought I was getting it all a bit wrapped around in my head.

Rikalous · Accepted Answer · 2013-07-08 09:01:21Z

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)

answered Jul 8, 2013 at 9:01

Rikalous

4,5491 gold badge42 silver badges53 bronze badges

Collectives™ on Stack Overflow

Using JOIN to UPDATE table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related