连接错误 1292 上的 MySQL 查询更新

Question

这是 MySql 数据库表中的一个 sql 查询尝试，其中 join 两个不同的表，用于为单个用户提取 PR_Cod 和 PR_Cod 的值:

SELECT
    CA.theDate,
    CA.theHour,
    CA.TheUSerCode,
    A.PR_Cod,
    A.PR_Name
FROM
    tblUserRegistered CA
JOIN tblUserAreaGlobal A ON A.TheUSerCode = CA.TheUSerCode
WHERE
    theDate BETWEEN DATE_SUB('2015-11-16', INTERVAL 1 DAY)
AND '2015-11-16'
AND (
    HOUR (theHour) >= '15:00:00'
    OR HOUR (theHour) <= '03:00:00'
)
AND A.PR_Cod IN ('058')
ORDER BY
    CA.TheUSerCode,
    CA.theDate,
    CA.theHour;

+------------+----------+-------------+--------+---------+
| theDate    | theHour  | TheUSerCode | PR_Cod | PR_Name |
+------------+----------+-------------+--------+---------+
| 2015-11-16 | 21:30:37 | EU4012111   | 058    | XXX1    |
| 2015-11-16 | 22:21:29 | EU4012111   | 058    | XXX1    |
| 2015-11-16 | 15:15:00 | EU4046905   | 058    | XXX1    |
+------------+----------+-------------+--------+---------+
3 rows in set

现在我需要更新第一个表 tblUserRegistered 上字段 Value 和 Text 的这三行。

我试过这个update join查询:

UPDATE tblUserRegistered AS target
INNER JOIN (
SELECT
    CA.theDate,
    CA.theHour,
    CA.TheUSerCode,
    A.PR_Cod,
    A.PR_Name
FROM
    tblUserRegistered CA
JOIN tblUserAreaGlobal A ON A.TheUSerCode = CA.TheUSerCode
WHERE
    theDate BETWEEN DATE_SUB('2015-11-16', INTERVAL 1 DAY)
AND '2015-11-16'
AND (
    HOUR (theHour) >= '15:00:00'
    OR HOUR (theHour) <= '03:00:00'
)
AND A.PR_Cod IN ('058')
ORDER BY
    CA.TheUSerCode,
    CA.theDate,
    CA.theHour) AS source ON source.TheUSerCode = target.TheUSerCode
SET target.Value = 1,
 target.Text = 'Y';

但是在输出中我没有更新只有三行提取的第一个查询而是更多的一行:

+------------+----------+-------------+-------+------+
| theDate    | theHour  | TheUSerCode | Value | Text |
+------------+----------+-------------+-------+------+
| 2015-11-16 | 22:21:29 | EU4012111   |     1 | Y    |
| 2015-11-16 | 21:30:37 | EU4012111   |     1 | Y    |
| 2015-11-16 | 15:15:00 | EU4046905   |     1 | Y    |
| 2015-11-16 | 04:22:13 | EU4046905   |     1 | Y    |
+------------+----------+-------------+-------+------+
4 rows in set

为什么查询用小时 04:22:13 更新行？

我试过:

UPDATE tblUserRegistered AS target
INNER JOIN (
SELECT
    CA.theDate,
    CA.theHour,
    CA.TheUSerCode,
    A.PR_Cod,
    A.PR_Name
FROM
    tblUserRegistered CA
JOIN tblUserAreaGlobal A ON A.TheUSerCode = CA.TheUSerCode
WHERE
    theDate BETWEEN DATE_SUB('2015-11-16', INTERVAL 1 DAY)
AND '2015-11-16'
AND (
    HOUR (theHour) >= '15:00:00'
    OR HOUR (theHour) <= '03:00:00'
)
AND A.PR_Cod IN ('058')
ORDER BY
    CA.TheUSerCode,
    CA.theDate,
    CA.theHour) AS source ON source.TheUSerCode = target.TheUSerCode
SET target.Value = 1,
 target.Text = 'Y'
    WHERE
        target.theDate BETWEEN DATE_SUB('2015-11-16', INTERVAL 1 DAY)
    AND '2015-11-16'
    AND (
        HOUR (target.theHour) >= '15:00:00'
        OR HOUR (target.theHour) <= '03:00:00'
    )
    AND source.PR_Cod IN ('058');

但在这种情况下我有错误:

[Err] 1292 - Truncated incorrect DOUBLE value: '15:00:00'

请帮助我，在此先感谢您。

Answer 1

你把自己复杂化了，简单地接受你的查询并使用 UPDATE 语法

UPDATE tblUserRegistered CA
JOIN tblUserAreaGlobal A 
ON A.TheUSerCode = CA.TheUSerCode
SET CA.Value = 1,
 CA.Text = 'Y'
WHERE
    theDate BETWEEN DATE_SUB('2015-11-16', INTERVAL 1 DAY)
AND '2015-11-16'
AND (
    HOUR (theHour) >= '15:00:00'
    OR HOUR (theHour) <= '03:00:00'
)
AND A.PR_Cod IN ('058')