MySQL - 基于 SELECT 查询的 UPDATE 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1262786/
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 - UPDATE query based on SELECT Query
提问by John M
I need to check (from the same table) if there is an association between two events based on date-time.
我需要检查（从同一张表中）是否存在基于日期时间的两个事件之间的关联。
One set of data will contain the ending date-time of certain events and the other set of data will contain the starting date-time for other events.
一组数据将包含某些事件的结束日期时间，另一组数据将包含其他事件的开始日期时间。
If the first event completes before the second event then I would like to link them up.
如果第一个事件在第二个事件之前完成，那么我想将它们链接起来。
What I have so far is:
到目前为止我所拥有的是：
SELECT name as name_A, date-time as end_DTS, id as id_A
FROM tableA WHERE criteria = 1
SELECT name as name_B, date-time as start_DTS, id as id_B
FROM tableA WHERE criteria = 2
Then I join them:
然后我加入他们：
SELECT name_A, name_B, id_A, id_B,
if(start_DTS > end_DTS,'VALID','') as validation_check
FROM tableA
LEFT JOIN tableB ON name_A = name_B
Can I then, based on my validation_check field, run a UPDATE query with the SELECT nested?
然后，我可以根据我的validation_check 字段运行带有 SELECT 嵌套的 UPDATE 查询吗？
回答by Eric
You can actually do this one of two ways:
您实际上可以通过以下两种方式之一执行此操作：
MySQL update join syntax:
MySQL 更新连接语法：
UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here
ANSI SQL syntax:
ANSI SQL 语法：
UPDATE tableA SET validation_check =
(SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
FROM tableA
INNER JOIN tableB ON name_A = name_B
WHERE id_A = tableA.id_A)
Pick whichever one seems most natural to you.
选择您认为最自然的那个。
回答by massquote
UPDATE
`table1` AS `dest`,
(
SELECT
*
FROM
`table2`
WHERE
`id` = x
) AS `src`
SET
`dest`.`col1` = `src`.`col1`
WHERE
`dest`.`id` = x
;
Hope this works for you.
希望这对你有用。
回答by Sergio
Easy in MySQL:
在 MySQL 中很容易：
UPDATE users AS U1, users AS U2
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id
回答by KMX
If somebody is seeking to update data from one database to another no matter which table they are targeting, there must be some criteria to do it.
如果有人试图将数据从一个数据库更新到另一个数据库，无论他们的目标是哪个表，都必须有一些标准来做到这一点。
This one is better and clean for all levels:
这个对所有级别都更好，更干净：
UPDATE dbname1.content targetTable
LEFT JOIN dbname2.someothertable sourceTable ON
targetTable.compare_field= sourceTable.compare_field
SET
targetTable.col1 = sourceTable.cola,
targetTable.col2 = sourceTable.colb,
targetTable.col3 = sourceTable.colc,
targetTable.col4 = sourceTable.cold
Traaa! It works great!
啊！它工作得很好！
With the above understanding, you can modify the set fields and "on" criteria to do your work. You can also perform the checks, then pull the data into the temp table(s) and then run the update using the above syntax replacing your table and column names.
有了上面的理解，您就可以修改设置的字段和“on”条件来完成您的工作。您还可以执行检查，然后将数据拉入临时表，然后使用上述语法替换您的表和列名称运行更新。
Hope it works, if not let me know. I will write an exact query for you.
希望它有效，如果没有让我知道。我会为你写一个确切的查询。
回答by Don
UPDATE
receipt_invoices dest,
(
SELECT
`receipt_id`,
CAST((net * 100) / 112 AS DECIMAL (11, 2)) witoutvat
FROM
receipt
WHERE CAST((net * 100) / 112 AS DECIMAL (11, 2)) != total
AND vat_percentage = 12
) src
SET
dest.price = src.witoutvat,
dest.amount = src.witoutvat
WHERE col_tobefixed = 1
AND dest.`receipt_id` = src.receipt_id ;
Hope this will help you in a case where you have to match and update between two tables.
希望这会在您必须在两个表之间匹配和更新的情况下对您有所帮助。
回答by sibaz
I found this question in looking for my own solution to a very complex join. This is an alternative solution, to a more complex version of the problem, which I thought might be useful.
我在为一个非常复杂的连接寻找自己的解决方案时发现了这个问题。这是该问题的更复杂版本的替代解决方案，我认为这可能有用。
I needed to populate the product_id field in the activities table, where activities are numbered in a unit, and units are numbered in a level (identified using a string ??N), such that one can identify activities using an SKU ie L1U1A1. Those SKUs are then stored in a different table.
我需要填充活动表中的 product_id 字段，其中活动以单位编号，单位以级别编号（使用字符串 ??N 标识），以便可以使用 SKU 标识活动，即 L1U1A1。然后将这些 SKU 存储在不同的表中。
I identified the following to get a list of activity_id vs product_id:-
我确定了以下内容以获取 activity_id 与 product_id 的列表：-
SELECT a.activity_id, w.product_id
FROM activities a
JOIN units USING(unit_id)
JOIN product_types USING(product_type_id)
JOIN web_products w
ON sku=CONCAT('L',SUBSTR(product_type_code,3), 'U',unit_index, 'A',activity_index)
I found that that was too complex to incorporate into a SELECT within mysql, so I created a temporary table, and joined that with the update statement:-
我发现这太复杂了，无法合并到 mysql 中的 SELECT，所以我创建了一个临时表，并用更新语句加入了它：-
CREATE TEMPORARY TABLE activity_product_ids AS (<the above select statement>);
UPDATE activities a
JOIN activity_product_ids b
ON a.activity_id=b.activity_id
SET a.product_id=b.product_id;
I hope someone finds this useful
我希望有人觉得这很有用
回答by bhavik
UPDATE [table_name] AS T1,
(SELECT [column_name]
FROM [table_name]
WHERE [column_name] = [value]) AS T2
SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];
回答by SergeyUr
You can use:
您可以使用：
UPDATE Station AS st1, StationOld AS st2
SET st1.already_used = 1
WHERE st1.code = st2.code
回答by Anand Roshan
You can update values from another table using inner join like this
您可以像这样使用内部联接更新另一个表中的值
UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.column1_name] = t2.[column1_name] SET t1.[column2_name] = t2.column2_name];
Follow here to know how to use this query http://www.voidtricks.com/mysql-inner-join-update/
按照这里了解如何使用此查询http://www.voidtricks.com/mysql-inner-join-update/
or you can use select as subquery to do this
或者您可以使用 select 作为子查询来执行此操作
UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];
query explained in details here http://www.voidtricks.com/mysql-update-from-select/
查询在此处详细解释http://www.voidtricks.com/mysql-update-from-select/
回答by Gnanam pragasam
For same table,
对于同一张桌子，
UPDATE PHA_BILL_SEGMENT AS PHA,
(SELECT BILL_ID, COUNT(REGISTRATION_NUMBER) AS REG
FROM PHA_BILL_SEGMENT
GROUP BY REGISTRATION_NUMBER, BILL_DATE, BILL_AMOUNT
HAVING REG > 1) T
SET PHA.BILL_DATE = PHA.BILL_DATE + 2
WHERE PHA.BILL_ID = T.BILL_ID;
