hi all,
On MySQL database I have two tables
tmp1
mysql> SELECT * FROM `tmp1`;
+-----+---------------------+-----------+----------------+--------+
| sID | sDate_sHour | sName | sStatus | sValue |
+-----+---------------------+-----------+----------------+--------+
| 1 | 2019-04-27 14:00:52 | user76681 | not registered | NULL |
| 2 | 2019-05-08 09:39:19 | user76681 | not registered | NULL |
+-----+---------------------+-----------+----------------+--------+
2 rows in set
And tmp2
mysql> SELECT * FROM `tmp2`;
+-----+---------------------+-----------+------------+
| sID | sDate_sHour | sName | sStatus |
+-----+---------------------+-----------+------------+
| 1 | 2019-05-08 09:36:14 | user76681 | registered |
+-----+---------------------+-----------+------------+
1 row in set
When I’ve on tmp2 for the same sName the sDate_sHour higher than the sDate_sHour of tmp1 I need update the column sValue with N value
return tmp1 after update
mysql> SELECT * FROM `tmp1`;
+-----+---------------------+-----------+----------------+--------+
| sID | sDate_sHour | sName | sStatus | sValue |
+-----+---------------------+-----------+----------------+--------+
| 1 | 2019-04-27 14:00:52 | user76681 | not registered | N |
| 2 | 2019-05-08 09:39:19 | user76681 | not registered | NULL |
+-----+---------------------+-----------+----------------+--------+
2 rows in set
because the date 2019-05-08 09:36:14 from tmp2 is higher of 2019-04-27 14:00:52 from tmp1
and the date 2019-05-08 09:36:14 from tmp2 is less of 2019-05-08 09:39:19 from tmp1
I’ve tried using MySQL 8.0.17 this query without success
mysql> UPDATE `tmp1` ttt
JOIN (
SELECT
jjj.sName AS jjjsName
FROM
`tmp1` jjj
JOIN `tmp2` kkk ON jjj.sName = kkk.sName
AND kkk.sDate_sHour > jjj.sDate_sHour
WHERE
jjj.sStatus IN ('not registered')
ORDER BY
kkk.sDate_sHour DESC
) AS hhh
SET ttt.sValue = 'N'
WHERE
ttt.sName = hhh.jjjsName;
Query OK, 2 rows affected
this return is wrong
mysql> SELECT
*
FROM
tmp1;
+-----+---------------------+-----------+----------------+--------+
| sID | sDate_sHour | sName | sStatus | sValue |
+-----+---------------------+-----------+----------------+--------+
| 1 | 2019-04-27 14:00:52 | user76681 | not registered | N |
| 2 | 2019-05-08 09:39:19 | user76681 | not registered | N |
+-----+---------------------+-----------+----------------+--------+
2 rows in set
I have no idea what the problem is.
Please can you help me?
Structure tables tmp1 and tmp2 below
DROP TABLE IF EXISTS `tmp1`;
CREATE TABLE `tmp1` (
`sID` int(11) NOT NULL AUTO_INCREMENT,
`sDate_sHour` datetime DEFAULT NULL,
`sName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`sStatus` varchar(255) DEFAULT NULL,
`sValue` char(1) DEFAULT NULL,
PRIMARY KEY (`sID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tmp1
-- ----------------------------
INSERT INTO `tmp1` VALUES ('1', '2019-04-27 14:00:52', 'user76681', 'not registered', null);
INSERT INTO `tmp1` VALUES ('2', '2019-05-08 09:39:19', 'user76681', 'not registered', null);
DROP TABLE IF EXISTS `tmp2`;
CREATE TABLE `tmp2` (
`sID` int(11) NOT NULL AUTO_INCREMENT,
`sDate_sHour` datetime DEFAULT NULL,
`sName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`sStatus` varchar(255) DEFAULT NULL,
PRIMARY KEY (`sID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tmp2
-- ----------------------------
INSERT INTO `tmp2` VALUES ('1', '2019-05-08 09:36:14', 'user76681', 'registered');