0

I uploaded my data in MySQL.

CREATE TABLE exercise1
    (`Plot_No` int, `Fertilizer` varchar(7), `Rep` varchar(3), 
   `Genotype` varchar(3), `Yield` int, `Biomass` int, `ID` varchar(15));
    
INSERT INTO exercise1
    (`Plot_No`, `Fertilizer`, `Rep`, `Genotype`, `Yield`, `Biomass`, `ID`)
VALUES
    (1, 'Control', 'I', 'CV1', 1190, 1150, '1ControlICV1'),
    (2, 'Control', 'I', 'CV2', 1240, 1220, '2ControlICV2'),
    (3, 'Control', 'I', 'CV3', 1240, 1230, '3ControlICV3'),
    (4, 'Fast', 'I', 'CV1', 980, 1090, '4FastICV1'),
    (5, 'Fast', 'I', 'CV2', 950, 1180, '5FastICV2'),
    (6, 'Fast', 'I', 'CV3', 1030, 1200, '6FastICV3'),
    (7, 'Slow', 'I', 'CV1', 1580, 1170, '7SlowICV1'),
    (8, 'Slow', 'I', 'CV2', 1580, 1180, '8SlowICV2'),
    (9, 'Slow', 'I', 'CV3', 1580, 1230, '9SlowICV3'),
    (10, 'Control', 'II', 'CV1', 1180, 1180, '10ControlIICV1'),
    (11, 'Control', 'II', 'CV2', 1140, 1180, '11ControlIICV2'),
    (12, 'Control', 'II', 'CV3', 1130, 1230, '12ControlIICV3'),
    (13, 'Fast', 'II', 'CV1', 910, 1270, '13FastIICV1'),
    (14, 'Fast', 'II', 'CV2', 950, 1240, '14FastIICV2'),
    (15, 'Fast', 'II', 'CV3', 880, 1280, '15FastIICV3'),
    (16, 'Slow', 'II', 'CV1', 1660, 1130, '16SlowIICV1'),
    (17, 'Slow', 'II', 'CV2', 1560, 1240, '17SlowIICV2'),
    (18, 'Slow', 'II', 'CV3', 1650, 1250, '18SlowIICV3'),
    (19, 'Control', 'III', 'CV1', 1210, 1058, '19ControlIIICV1'),
    (20, 'Control', 'III', 'CV2', 1310, 1200, '20ControlIIICV2'),
    (21, 'Control', 'III', 'CV3', 1270, 1310, '21ControlIIICV3'),
    (22, 'Fast', 'III', 'CV1', 980, 1380, '22FastIIICV1'),
    (23, 'Fast', 'III', 'CV2', 950, 1250, '23FastIIICV2'),
    (24, 'Fast', 'III', 'CV3', 960, 1230, '24FastIIICV3'),
    (25, 'Slow', 'III', 'CV1', 1540, 1320, '25SlowIIICV1'),
    (26, 'Slow', 'III', 'CV2', 1580, 1220, '26SlowIIICV2'),
    (27, 'Slow', 'III', 'CV3', 1500, 1290, '27SlowIIICV3');

Then, I changed texts in Genotype column

UPDATE exercise1
SET Genotype= REPLACE(REPLACE(REPLACE(Genotype, "CV1", "Genotype1"), "CV2", "Genotype2"), "CV3", "Genotype3");

enter image description here



All of sudden, I changed my mind, and I want to undo about what I did. Could you tell me how I can revert to the original data table? If this question is duplicated, it would be great to link the answers.

Many thanks,
Jin.w.Kim
  • 599
  • 1
  • 4
  • 15
  • Maybe this "undo" feature can help you? https://stackoverflow.com/questions/2918831/how-can-i-undo-a-mysql-statement-that-i-just-executed –  Feb 17 '22 at 14:45
  • DO you have a backup? – P.Salmon Feb 17 '22 at 14:45
  • What keeps you from dumping the table, and adidng it again according to the first snippet? – Nico Haase Feb 17 '22 at 14:47
  • Thank you for your all comments. I just start to learn MySQL, and not familiar with what you told me. Simply, if you have the same data above, and changed the texts in a column, and want to change it originally, how can you do that? Do you use any specific codes? If so, could you write here so that I can copy it? Always many thanks!!! – Jin.w.Kim Feb 17 '22 at 14:55
  • 'I just start to learn MySQL' If what you mean by this is not learning about back up and recovery procedures and not about transactions then as suggested DROP the table and rerun your create and insert code. – P.Salmon Feb 17 '22 at 14:59
  • When you really defined `Genotype` as `varchar(3)`, then the update should have produced this error: `ERROR 1406 (22001): Data too long for column 'Genotype' at row 1`, and noting should have changed. – Luuk Feb 17 '22 at 14:59
  • @P.Salmon Thank you for your answer. Now every time I made one mistake, I drop the table and create data table again and write codes again. That's why I want to know how to undo. Backup means START TRANSACTION? Could you link any website regarding the "back up"? – Jin.w.Kim Feb 17 '22 at 15:04
  • @Luuk Yes I recognized this issues and showed the error message you mentioned. I simply changed as varchar(20) and there are no error messages anymore. – Jin.w.Kim Feb 17 '22 at 15:05
  • "All of sudden, I changed my mind" .... You should think before you act! – Luuk Feb 17 '22 at 15:07
  • https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html https://dev.mysql.com/doc/refman/8.0/en/commit.html also nothing to stop you saving working code to any text file ,then copy ,pasting to an ide and running it. – P.Salmon Feb 17 '22 at 15:08
  • @Luuk There will be no error if the SQL mode does not include strict mode. MySQL will just silently truncate the strings and store the first 3 characters. In this case, it loses all the useful information about the Genotype, because the first 3 characters are identical. – Bill Karwin Feb 17 '22 at 16:13
  • @BillKarwin: OK, but the screenimage shows the complete updated text... – Luuk Feb 17 '22 at 17:03
  • You're right, and an UPDATE cannot change the data type of a column, so the OP must have been mistaken about their table definition. The column cannot be VARCHAR(3). – Bill Karwin Feb 17 '22 at 17:06
  • 1
    First you need an `ALTER TABLE` to make room for the longer genotypes. Then I would not use `REPLACE`. Instead 3 `UPDATEs` like `UPDATE exercise1 SET Genotype = "genotype1" WHERE Genotype = "CV1";` – Rick James Feb 17 '22 at 20:36
  • @RickJames Thank you so much!! I created codes like below. alter table data_analysis.exercise1 change column `Genotype` `Genotype` varchar(20) null default null; set sql_safe_updates= 0; UPDATE exercise1 SET Genotype = "genotype1" WHERE Genotype = "CV1"; UPDATE exercise1 SET Genotype = "genotype2" WHERE Genotype = "CV2"; UPDATE exercise1 SET Genotype = "genotype3" WHERE Genotype = "CV3"; Then when I closed MySQL and open it again. The data table does not save!!! Thanks a lot!!! – Jin.w.Kim Feb 19 '22 at 09:18
  • @Jin.W.Kim - Avoid "autocommit=0"; if you forget to do a `COMMIT`, the changes are thrown away. – Rick James Feb 19 '22 at 16:31

0 Answers0