根据其ID更新所有等于另一个表的列值的所有列值 [英] UPDATE all column values equivalent to another tables column value based on their id
问题描述
我有这个叫 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 |
+---------+---------+------------+----------+
并且我还有这张名为 item 的表:
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 |
+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-----------+------------+
我主要想这样做: 根据 item 表的* item_price *更新 shop 表的* item_price *.
I mainly want to do this: update the *item_price* of the shop table based on the *item_price* of the item table.
免责声明
询问此查询的目的是从item item_price表中填写我的商店item_price.但是将来的商店item_price的价值将与item_price的价值不同.
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.
例如:
项目将使用商店的item_price.
出售给商店的商品将基于item_price.
item bought from the shop will use the shop item_price.
item sold to the shop will base from the item_price.
推荐答案
您可以简单地通过连接表来做到这一点.
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
- SQLFiddle演示
- SQLFiddle Demo
执行UPDATE语句后的
OUTPUT
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 ║
╚═════════╩═════════╩════════════╩══════════╝
这篇关于根据其ID更新所有等于另一个表的列值的所有列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
