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

Multiply two values ​​in a variable

php replace

I have a string wich contains 2 values:

WordPress Template Directory

wordpress

How can I get this to work? Thanks in advance!!! I think the first echo should work but it doesn't...it prints out the URL in text instead of showing the image...