How to write a SQL query to update a table based of a result in another table

Question

I have 2 different tables in my database and can't find any reference on how to use a result to update a part of a table.

Here is my scenario

Table: MenuItems

╔════╦══════════════╦
║ id ║  Name        ║
╠════╬══════════════╬
║  1 ║ test         ║
║  2 ║ test2        ║
╚════╩══════════════╩

Table: MenuItemPrices

╔════╦══════════════╦
║ id ║  Price       ║
╠════╬══════════════╬
║  1 ║ 3.50         ║
║  2 ║ 4.50         ║
╚════╩══════════════╩

Say I want to update test2 price to 5.00, what would be the query I need?

Cant find any reference? Had you typed your question into google, you would have found more than a few [including this one](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Gary Walker, May 16 '16 at 01:09
@GaryWalker I did google it and I did find that problem, but I felt that question did not sufficiently explain what I was attemtping to acheive and if you see the answers I got from this question were sufficiently different from that question. — bgscoffeeprogram, May 16 '16 at 01:15

score 0 · Accepted Answer · answered May 16 '16 at 00:03

0

UPDATE p
SET    Price = 5.00
FROM   MenuItems i
INNER JOIN MenuItemPrice p ON i.id = p.id
WHERE  i.Name = 'test2'

answered May 16 '16 at 00:03

Squirrel

score 0 · Answer 2 · answered May 16 '16 at 01:01

0

UPDATE p
SET Price = 5.00
FROM   
    MenuItems i,
    MenuItemPrice p 
WHERE 
    i.id = p.id AND
    i.Name = 'test2'

answered May 16 '16 at 01:01

openwonk

2 Answers2