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

2017-04-20 09:18:46

其他回答

简单的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

INSERT INTO all_table
SELECT Orders.OrderID,
    Orders.CustomerID,
    Orders.Amount,
    Orders.ProductID,
    Orders.Date,
    Customer.CustomerName,
    Customer.Address
FROM Orders
JOIN Customer ON Orders.CustomerID=Customer.CustomerID
WHERE Orders.OrderID not in (SELECT OrderID FROM all_table)

2021-11-09 08:28:48

你可以使用:

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

2017-03-03 06:29:09

UPDATE
    `table1` AS `dest`,
    (
        SELECT
            *
        FROM
            `table2`
        WHERE
            `id` = x
    ) AS `src`
SET
    `dest`.`col1` = `src`.`col1`
WHERE
    `dest`.`id` = x
;

希望这对你有用。

2012-12-12 09:03:42

我在寻找一个非常复杂的连接的解决方案时发现了这个问题。这是另一种解决方案，一个更复杂的问题，我认为可能有用。

我需要填充活动表中的product_id字段，其中活动以单位编号，单位以级别编号(使用字符串??N标识)，这样就可以使用SKU(即L1U1A1)标识活动。然后将这些sku存储在另一个表中。

我确定了以下内容以获得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)

我发现这是太复杂，以纳入一个SELECT在mysql，所以我创建了一个临时表，并加入了更新语句:-

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;

我希望有人觉得这有用

2014-08-15 14:39:33

MySQL - UPDATE查询基于SELECT查询

推荐文章

最新文章

标签