MySQL查询以增加日期更新记录 [英] MySQL query to update records with incremented date

问题描述

我正在尝试

1. 获取数据库中的最新日期，并且


2. 在该日期更新每个具有 NULL 日期的记录，将日期增加1天。

1. get the latest date in a database, and
2. based on that date update every record that has a NULL date, increasing the date by 1 day.

我可以使用下面的最新日期查询获取最新的日期。我需要这样做，因为表中的日期是不是。如果需要，我可以运行这个查询，手动写下来，然后根据这个日期运行 UPDATE 查询。我不喜欢在没有手动过程的情况下运行所有​​内容。

I can get the latest date by using the Latest Date query below. I need to do this first because the dates in the table are not in order. If need be, I can run this query, manually write it down, then run the UPDATE query based on this date. I would prefer to run everything without the manual process.

我在问题底部的最后一个查询是我的测试查询更新日期，但是我没有运气让它上班。

The last query I have at the bottom of the question is my test query for trying to update the dates, however I had no luck getting it to work.

表（日期不顺序）

id     date
-----  ----------
10500  2013-08-18
10501  2013-08-16
10502  2013-08-17
10503  2013-08-19
10504  NULL
10505  NULL
...
11800  NULL
11801  NULL

选择最新日期（起始点 UPDATE ）

Selecting the latest date (starting point for UPDATE)

SELECT date
FROM my_table
ORDER BY date DESC
LIMIT 1

更新 NULL 日期（不工作）

Updating NULL dates (doesn't work)

UPDATE my_table
SET date = DATE_ADD((SELECT date FROM my_table ORDER BY date DESC LIMIT 1), INTERVAL 1 DAY)
WHERE date IS NULL
ORDER BY id ASC

我该如何完成？还是这不可能？

How can I accomplish this? Or is this not possible?

推荐答案

尝试

UPDATE Table1 t1 JOIN
(
  SELECT id, @n := @n + 1 rnum
    FROM Table1 CROSS JOIN (SELECT @n := 0) i
   WHERE date IS NULL
   ORDER BY id
) t2 ON t1.id = t2.id CROSS JOIN
(
  SELECT MAX(date) date FROM Table1
) q
   SET t1.date = q.date + INTERVAL t2.rnum DAY

结果：

|    ID |       DATE |
----------------------
| 10500 | 2013-08-18 |
| 10501 | 2013-08-16 |
| 10502 | 2013-08-17 |
| 10503 | 2013-08-19 |
| 10504 | 2013-08-20 | --  date has been assigned
| 10505 | 2013-08-21 | --  date has been assigned

这里是 SQLFiddle 演示

Here is SQLFiddle demo

说明：在具有别名t2的子查询中，我们抓住所有其中date IS NULL的行通过 id 命令并分配从1开始的行号。不幸的是MySql没有执行 ROW_NUMBER（）函数，所以我们用一个用户变量 @n 来执行它，当行被选中时它会递增。要初始化此变量，我们使用一个别名为 i 的子查询。并使用 CROSS JOIN 使其可用于我们的子查询 t2 。然后我们使用相同的技术（ CROSS JOIN ）来获取表中的最大日期，并使其适用于我们 JOIN 。 ONce，我们只需要添加一个行号，代表多天），将其添加到最大日期并分配给我们的表中的 date 列。

Explanation: In a subquery with an alias t2 we grab all rows where date IS NULL order them by id and assign row numbers starting from 1. Unfortunately MySql doesn't have an implementation for ROW_NUMBER() function so we do it with a user variable @n which is incremented while rows are selected. To initialize this variable we use a subquery with an alias i. And use CROSS JOIN to make it available for our subquery t2. We then use the same technique (CROSS JOIN) to grab a max date in the table and make it available for every row in our JOIN. ONce we have all that we just add a line number, which represents a number of days) add it to the max date and assign to date column in our table.

这篇关于MySQL查询以增加日期更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！