Update join table using MySQL 8.0.17[Answered] RSS

• • Reply
  Chevy Marl S...

  Member

  49 Points

  77 Posts

  Update join table using MySQL 8.0.17

  May 25, 2020 04:57 PM|Chevy Marl Sunderland|LINK

  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
  

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

• • Reply
  gapimex

  Member

  310 Points

  76 Posts

  Re: Update join table using MySQL 8.0.17

  May 26, 2020 12:33 PM|gapimex|LINK

  Hi,

  Try:

  update tmp1
  inner join tmp2 on tmp2.sName = tmp1.sName
  set tmp1.sValue = 'N'
  where tmp1.sDate_sHour < tmp2.sDate_sHour

  Hope this help

  Signature: http://www.imoveisemexposicao.com.br/imoveis-alugar-guarulhos-comercial_ou_industrial-galpão