How to update the date of existing records?



  • Can you help me ? I've searched a lot and haven't found anything similar to what I need to do, thanks!

    I have a table with 4 columns (CPF, NAME, PHONE, DT_IMP)

    I need to update the DT_IMP column to the latest date if I receive an existing record in the PHONE column.

    Example:

    It is like this...

    12312312312|JOAO | 21912345678|2021-12-09

    If I receive the PHONE 21912345678 again to be imported, the date must be updated to the day of import.

    12312312312|JOAO | 21912345678|2022-01-31



  • You should use https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html .

    Keep in mind that PHONE column should be declared as PRIMARY KEY.

    Follow below example: Suppose we have below data and we are trying to insert a matching PHONE value which is 21912345678.

    CREATE TABLE test_tbl (
    CPF bigint ,
    NAME varchar(30),
    PHONE varchar(30) ,
    DT_IMP date,
    primary key PHONE(`PHONE`) );
    

    insert into test_tbl values
    (12312312312,'JOAO',21912345678,'2021-12-09'),
    (12312312313,'JOAO',21912345679,'2021-12-10'),
    (12312312314,'JOAO',21912345656,'2021-12-11');

    Using:

    INSERT INTO test_tbl (`CPF`,`NAME`,`PHONE`,`DT_IMP`)
    VALUES (12312312315,'BEN',21912345678, '2022-01-31') 
    ON DUPLICATE KEY UPDATE  `DT_IMP`=VALUES(`DT_IMP`);
    

    The result would be:

    CPF           NAME     PHONE        DT_IMP
    12312312312   JOAO   21912345678    2022-01-31
    12312312313   JOAO   21912345679    2021-12-10
    12312312314   JOAO   21912345656    2021-12-11
    

    Now if you want other value to change like CPF or NAME.

    Use:

    INSERT INTO test_tbl (`CPF`,`NAME`,`PHONE`,`DT_IMP`)
        VALUES (12312312315,'BEN',21912345678, '2022-01-31') 
        ON DUPLICATE KEY UPDATE  `CPF`=VALUES(`CPF`),`NAME`=VALUES(`NAME`),`DT_IMP`=VALUES(`DT_IMP`);
    

    Result:

        CPF          NAME     PHONE         DT_IMP
    12312312313      JOAO   21912345679   2021-12-10
    12312312314      JOAO   21912345656   2021-12-11
    12312312315      BEN    21912345678   2022-01-31
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8beb6a47a1c70919ee6b37ce9fbba291


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2