MySQL Forums
Forum List  »  Newbie

Update existing value on column using a database MySql version 8.0.17
Posted by: Chevy Mark Sunderland
Date: April 29, 2021 11:43AM

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, thanks in advance for any help.

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.