CODEWITHSUNDEEP
mysqlsqlmariadb
Josh
Josh

Reputation: 618

MySQL Duplicate Row and Update both old and new rows

I need to duplicate a row in SQL, update one column in the old row and update one column in the new row.

Using MariaDB/MySQL

id attribute value start_date end_date
1 attribute_x 0 0 null
2 attribute_y 1 0 null

I want to:

  1. Get all rows where attribute = attribute_x
  2. Set the end_date of that row to the current timestamp.
  3. Duplicate that row.
  4. On the new row, set the start_date to the current timestamp.
  5. On the new row, set the end_date to null.

Upvotes: 0

Views: 134

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

This is two operations:

update t
    set end_date = now()
    where attribute = 'attribute_x';

insert into t (attribute, value, start_date, end_date)
    select attribute, value, now(), null
    from t
    where attribute = 'attribute_x';

Upvotes: 1

Related Questions