UPDATE all column values equivalent to another tables column value based on their id

I have this table called shop:

+---------+---------+------------+----------+
| shop_id | item_id | item_price | item_qty |
+---------+---------+------------+----------+
|       1 |       1 |          0 |       99 |
|       2 |       2 |          0 |       99 |
|       3 |       3 |          0 |       99 |
|       4 |       4 |          0 |       99 |
|       5 |       5 |          0 |       99 |
|       6 |       6 |          0 |       99 |
|       7 |       7 |          0 |       99 |
|       8 |       8 |          0 |       99 |
+---------+---------+------------+----------+

and i also have this table called item:

+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-----------+------------+
| item_id | item_name       | item_type | item_atk | item_def | item_atr | item_img                        | item_desc | item_price |
+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-----------+------------+
|       0 | Halberd         |         1 |      220 |       20 |        0 | pics/weapons/halberd.png        |           |        400 |
|       1 | Axe             |         1 |      220 |       10 |        0 | pics/weapons/axe.png            |           |        200 |
|       2 | Wooden Sword    |         1 |       70 |        0 |        0 | pics/weapons/wooden-sword.png   |           |        225 |
|       3 | Dagger          |         1 |       60 |        5 |        0 | pics/weapons/dagger.png         |           |         55 |
|       4 | Bow             |         1 |      120 |        1 |        0 | pics/weapons/bow.png            |           |        120 |
|       5 | Helmet          |         4 |        0 |       50 |        0 | pics/headgears/helmet.png       |           |        155 |
|       6 | Tunic           |         2 |       10 |       10 |        0 | pics/armors/tunic.png           |           |         50 |
|       7 | Armour          |         2 |        0 |       75 |        0 | pics/armors/armour.png          |           |        150 |
|       8 | Necklace        |         3 |       25 |       15 |        0 | pics/accessories/necklace.png   |           |        199 |
|       9 | Studded Leather |         2 |       25 |       60 |        0 | pics/armors/studded-leather.png |           |        240 |
+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-----------+------------+

I mainly want to do this: update the *item_price* of the shop table based on the *item_price* of the item table.

disclaimer

the purpose of asking this query is to fill up my shop item_price from the item item_price table. but in the future shop item_price will have different value from item item_price.

e.g:

item bought from the shop will use the shop item_price. item sold to the shop will base from the item_price.

Answers


You can simply do this by joining the tables.

UPDATE  shop a
        INNER JOIN item b
            ON b.item_ID = a.item_ID
SET     a.item_price = b.item_price 

OUTPUT after the UPDATE statement has been executed

╔═════════╦═════════╦════════════╦══════════╗
║ SHOP_ID ║ ITEM_ID ║ ITEM_PRICE ║ ITEM_QTY ║
╠═════════╬═════════╬════════════╬══════════╣
║       1 ║       1 ║        200 ║       99 ║
║       2 ║       2 ║        225 ║       99 ║
║       3 ║       3 ║         55 ║       99 ║
║       4 ║       4 ║        120 ║       99 ║
║       5 ║       5 ║        155 ║       99 ║
║       6 ║       6 ║         50 ║       99 ║
║       7 ║       7 ║        150 ║       99 ║
║       8 ║       8 ║        199 ║       99 ║
╚═════════╩═════════╩════════════╩══════════╝

Need Your Help

Update storyboard constraint using code

ios swift uistoryboard

I have a UIImage in code that I would like to enlarge vertically when a button is pressed. The UIImage is fully constrained in the storyboard and I would like to change its height when the button is

calling dot products and linear algebra operations in Cython?

python numpy scipy cython blas

I'm trying to use dot products, matrix inversion and other basic linear algebra operations that are available in numpy from Cython. Functions like numpy.linalg.inv (inversion), numpy.dot (dot produ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.