php MySQL“REPLACE INTO”使用SELECT拉出一个字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8965047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL "REPLACE INTO" using a SELECT to pull one field
提问by psyklopz
I've got a MySQL database that has two tables (actually many more). The first table links a product's SKU number to an arbitrary ID. There is a second table that records the End of Day inventory for each item based on this ID. When the inventory is changed for reasons OTHER than sales, there is a record placed in this second table with a Boolean set to false. This allows me to say that this new number is not valid as a vector for sales previous, but is for the next day's sales.
我有一个 MySQL 数据库，它有两个表（实际上还有更多）。第一个表将产品的 SKU 编号链接到任意 ID。第二个表根据此 ID 记录每个项目的日终库存。当库存因销售以外的原因发生变化时，第二个表中会放置一条记录，布尔值设置为 false。这让我可以说这个新数字不能作为前一天销售的向量，但对于第二天的销售是有效的。
There is some syntax error in this code. I'm still a student, and would greatly appreciate the help in explaining how this kind of update would work. I know the first value needs to come from the select statement?
此代码中有一些语法错误。我仍然是一名学生，非常感谢在解释这种更新如何工作方面的帮助。我知道第一个值需要来自 select 语句？
Here is my current MySQL statement:
这是我当前的 MySQL 语句：
REPLACE INTO sales (`itemID`, `date`, `qty`, `price`)
VALUES ([itemID], CURDATE(), [qty], 0.00)
SELECT itemID FROM item WHERE `sku` = [sku]
回答by piotrm
Replace works like insert, except that if there is a row with the same key you are trying to insert, it will be deleted on replace instead of giving you an error.
替换的工作方式与插入类似，不同之处在于，如果有一行与您尝试插入的键相同，它将在替换时被删除，而不是给您一个错误。
You can either specify arguments directly:
您可以直接指定参数：
REPLACE INTO sales( `item_id`, `date`, `qty`, `price` )
VALUES( 15, '2012-01-01`, 5, '120.00' )
or specify them using SELECT:
或使用 SELECT 指定它们：
REPLACE INTO sales( `item_id`, `date`, `qty`, `price` )
SELECT item_id, date, qty, price FROM sales_to_accept
WHERE sales_id = 721
You cannot however mix both types of syntax in one query.
但是，您不能在一个查询中混合使用两种类型的语法。
But there is nothing stopping you from adding constant values as columns for the SELECT:
但是没有什么可以阻止您将常量值添加为 SELECT 的列：
REPLACE INTO sales( `item_id`, `date`, `qty`, `price` )
SELECT item_id, CURDATE(), 5, '74.00' FROM item
WHERE `sku` = 'something'
回答by Francis Lewis
You're trying to do a replace into and a select in the same statement.
If you're trying to run this using a program of some sort or simply inputting it into MySQL, if you place a semi-colon after the )at the end of the values section, it will treat it as 2 separate requests.
您正在尝试在同一语句中进行替换和选择。如果您尝试使用某种程序运行它或只是将其输入到 MySQL 中，如果您)在值部分的末尾放置一个分号，它将把它视为 2 个单独的请求。
If you're running this through PHP, then you'll need to break it up into 2 separate statements.
如果您通过 PHP 运行它，那么您需要将其分解为 2 个单独的语句。
REPLACE INTO sales (`itemID`, `date`, `qty`, `price`)
VALUES ((SELECT itemID FROM item WHERE `sku` = [sku]), CURDATE(), [qty], 0.00)
