if same columns values are repeated in multiple rows then UPDATE - SQL / MYSQL

Question

• If the same column values are repeated in the multiple rows

• then delete the whole row based on the column value and

• update the new row ( that is other column values ) based on the same column value

I have a table like this

create table if not exists cms(
  id int(11) not null auto_increment primary key,
  cms_id varchar(20),
  published_datetime varchar(30),
  last_modified_datetime varchar(30),
  score float,
  access_vector varchar(45),
  access_complexity varchar(45),
  authentication varchar(45),
);


INSERT INTO `cms` (`id`, `cms_id`, `published_datetime`, `last_modified_datetime`, `score`, `access_vector`, `access_complexity`, `authentication`) VALUES
(1, 'CMS-2002-0493', '2002-08-12T00:00:00.000-04:00', '2016-10-24T11:23:01.940-04:00', 7.5, 'NETWORK', 'LOW', 'NONE')

• now in this table I don't want to allow duplicate values for the column named cms_id

• For this I don't want to set unique key for the column cms_id
  because this column value will be repeated then based on this column value cms_id the other column values could be changed

• That is cms_id could be same where other columns could get
  different entries

• each time with the same cms_id values other columns in the row may different entries

in simple words if same value of cms_id gets repeated then update the whole row (update all columns based on cms_id)

now I need an output like only the last inserted cms_id value should be present in table other all same cms_id should be deleted and updated with last entry

coulmn id with primary key 1,613,1225 should be deleted and only 1837 should be present

I need output something like below image

totally it should replace the repeated cms_id column as per last entry.

accordingly if cms_id value repeats then it should delete the existing cms_id value and it should update all the other column values as per last insert entry , need query for this

Answer 1

MySQL has a special REPLACE command that works exactly like INSERT except it replaces the row if the primary key or an unique has the same value.

REPLACE INTO cms ...

Where ... represents the rest of the query.

Disclaimer: This SQL statement is a special extension made by MySQL; it is not part of the SQL standard and probably won't work on other DBMS.