MySQL - UPDATE查询基于SELECT查询

我需要检查(从同一个表中)两个事件之间是否存在基于日期-时间的关联。

一组数据将包含某些事件的结束日期-时间，另一组数据将包含其他事件的开始日期-时间。

如果第一个事件在第二个事件之前完成，那么我想把它们联系起来。

到目前为止，我有:

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

然后我加入他们:

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

然后，我可以根据validation_check字段运行一个带有SELECT嵌套的UPDATE查询吗?

当前回答

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];

2016-11-19 14:02:19

其他回答

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];

2016-11-19 14:02:19

实际上你有两种方法:

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语法:

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)

挑一个对你来说最自然的。

2009-08-11 20:53:47

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 ;

希望这将帮助你在一个情况下，你必须匹配和更新两个表。

2014-07-06 16:45:05

简单的MySQL:

UPDATE users AS U1, users AS U2 
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id

2015-04-18 02:53:39

你可以使用:

UPDATE Station AS st1, StationOld AS st2
   SET st1.already_used = 1
 WHERE st1.code = st2.code

2017-03-03 06:29:09

MySQL - UPDATE查询基于SELECT查询

推荐文章

最新文章

标签