Update existing value on column using a database MySql version 8.0.17

Databases
1

Hi,

I need update the existing value on column rowNumber on the table t3 using a database MySql version 8.0.17

On the table t3 are stored two rows for each date… and I need this return… that is to associate value 1 to the date with the oldest id and value 2 to the date with the most recent id

+-----------+------------+-----+
| rowNumber | data       | sID |
+-----------+------------+-----+
|         2 | 2019-04-30 |  22 |
|         1 | 2019-04-30 |  21 |
|         2 | 2019-04-29 |  20 |
|         1 | 2019-04-29 |  19 |
|         2 | 2019-04-28 |  18 |
|         1 | 2019-04-28 |  17 |
|         2 | 2019-04-27 |  16 |
|         1 | 2019-04-27 |  15 |
|         2 | 2019-04-26 |  14 |
|         1 | 2019-04-26 |  13 |
|         2 | 2019-04-25 |  12 |
|         1 | 2019-04-25 |  11 |
|         2 | 2019-04-24 |  10 |
|         1 | 2019-04-24 |   9 |
|         2 | 2019-04-23 |   8 |
|         1 | 2019-04-23 |   7 |
|         2 | 2019-04-22 |   6 |
|         1 | 2019-04-22 |   5 |
|         2 | 2019-04-21 |   4 |
|         1 | 2019-04-21 |   3 |
|         2 | 2019-04-20 |   2 |
|         1 | 2019-04-20 |   1 |
+-----------+------------+-----+

Any suggestion?

My table structure below or db-fiddle.com , which offers MySQL 8

-- ----------------------------
-- Table structure for t3
-- ----------------------------
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3`  (
  `rowNumber` int(10) NOT NULL,
  `data` date NULL DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  INDEX `data`(`data`) USING BTREE
) ENGINE = MyISAM;

-- ----------------------------
-- Records of t3
-- ----------------------------
INSERT INTO `t3` VALUES (3852, '2019-04-30', 22);
INSERT INTO `t3` VALUES (3851, '2019-04-30', 21);
INSERT INTO `t3` VALUES (3850, '2019-04-29', 20);
INSERT INTO `t3` VALUES (3849, '2019-04-29', 19);
INSERT INTO `t3` VALUES (3848, '2019-04-28', 18);
INSERT INTO `t3` VALUES (3847, '2019-04-28', 17);
INSERT INTO `t3` VALUES (3846, '2019-04-27', 16);
INSERT INTO `t3` VALUES (3845, '2019-04-27', 15);
INSERT INTO `t3` VALUES (3844, '2019-04-26', 14);
INSERT INTO `t3` VALUES (3843, '2019-04-26', 13);
INSERT INTO `t3` VALUES (3842, '2019-04-25', 12);
INSERT INTO `t3` VALUES (3841, '2019-04-25', 11);
INSERT INTO `t3` VALUES (3840, '2019-04-24', 10);
INSERT INTO `t3` VALUES (3839, '2019-04-24', 9);
INSERT INTO `t3` VALUES (3838, '2019-04-23', 8);
INSERT INTO `t3` VALUES (3837, '2019-04-23', 7);
INSERT INTO `t3` VALUES (3836, '2019-04-22', 6);
INSERT INTO `t3` VALUES (3835, '2019-04-22', 5);
INSERT INTO `t3` VALUES (3834, '2019-04-21', 4);
INSERT INTO `t3` VALUES (3833, '2019-04-21', 3);
INSERT INTO `t3` VALUES (3832, '2019-04-20', 2);
INSERT INTO `t3` VALUES (3831, '2019-04-20', 1);
2

so what have you tried?

3

this is such a weird request, i just had to give it a try

SELECT r1  AS id
     , data
     , sID
  FROM ( SELECT data
              , sID
              , ROW_NUMBER() OVER(PARTITION BY data
                                      ORDER BY sID  ASC) AS r1 
           FROM t3 ) AS t31
 WHERE r1 = 1 		
UNION ALL 	 
SELECT r2 + 1
     , data
     , sID
  FROM ( SELECT data
              , sID
              , ROW_NUMBER() OVER(PARTITION BY data
                                      ORDER BY sID DESC) AS r2 
           FROM t3 ) AS t31
 WHERE r2 = 1 	
ORDER
    BY DATA DESC
	  , sID  DESC

as for this part of the request –

… you’re on your own

;o)

1 Like
4

thanks for reply.

I’ve update the table using this query… what do you think?

UPDATE t3 t
JOIN (
	SELECT
		r1 AS id,
		DATA,
		sID 
	FROM
		( SELECT DATA, sID, ROW_NUMBER() OVER ( PARTITION BY DATA ORDER BY sID ASC ) AS r1 FROM t3 ) AS t3 
	WHERE
		r1 = 1 UNION ALL
	SELECT
		r2 + 1,
		DATA,
		sID 
	FROM
		( SELECT DATA, sID, ROW_NUMBER() OVER ( PARTITION BY DATA ORDER BY sID DESC ) AS r2 FROM t3 ) AS t3 
	WHERE
		r2 = 1 
	ORDER BY
		DATA DESC,
		sID DESC 
	) q 
	SET t.rowNumber = q.id 
WHERE
	t.sID = q.sID;
5

you’re very brave

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.