Update statement using INNER JOIN

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

Update statement using INNER JOIN

Author

Topic

alecd
Starting Member

7 Posts

Posted - 2011-03-09 : 00:26:45

Hello,

I'm wanting to do something like this:

UPDATE _ShoppingCart
SET Price = (_TShirtGroups.Price * .08)
WHERE CartId = @CartId
INNER JOIN _TShirts ON _ShoppingCart.ProductId = _TShirts.Id AND
INNER JOIN _TShirtGroups ON _TShirts.GroupId = _TShirtGroups.Id

What would be the best way to do this?

Thank you,

Alec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 00:32:46

UPDATE s
SET Price = tg.Price * .08
FROM _ShoppingCart s
JOIN _TShirts t ON s.ProductId = t.Id
JOIN _TShirtGroups tg ON t.GroupId = tg.Id
WHERE s.CartId = @CartId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-09 : 00:40:59

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

You did not really put an underscore at the front of a data element name, did you?

Why do you believe in a magical, universal “id”? To be is to be something in particular. Where is the UPC or SKU? Here is a wild guess, based on what you posted – which was almost nothing.

UPDATE ShoppingCart
SET purchase_price
= (SELECT inventory_price * 0.08)
FROM Tshirts AS T
WHERE T.sku = ShoppingCart.sku
WHERE cart_id = @in_cart_id;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 00:48:18

My post was nicer.