0

I would like to create new data in a table based on a previous data.

The table contains 11 columns. And the following SQL statement should be executed.

Where the column isOnPause returns true and the column endDate contains '%10:00%' then INSERT INTO table almost the same data with endDate="" AND a new ID generated for each row.

Any help appreciated.

Larry Petshow
  • 27
  • 6
  • This might help https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table – Amir Saleem Mar 29 '22 at 07:37
  • Self-insertions like `INSERT INTO FOO ... (SELECT ... FROM FOO WHERE ...)` are not possible, Use a [temporary table](https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html). – Joop Eggen Mar 29 '22 at 07:40

1 Answers1

1

You can use the following syntax.
NB the syntax endDate like '%10:00%' works in MySQL 8 but is not accepted in MySQL 5 so I have used an explicit CAST(endDate AS CHAR(20)) which works in both.

create table t(
id int auto_increment primary key,
isOnPause tinyint,
endDate datetime,
otherCol varchar(10));
insert into t (isOnPause, endDate,otherCol) values
(1,'2022-03-29 10:00:00','other 1'),
(0,'2022-03-29 10:00:00','other 2'),
(1,'2022-03-29 11:00:00','other 3'),
(0,'2022-03-29 11:00:00','other 4');

select * from t 
where isOnPause 
and endDate like  '%10:00%' ;

id | isOnPause | endDate             | otherCol
-: | --------: | :------------------ | :-------
 1 |         1 | 2022-03-29 10:00:00 | other 1 

insert into t (isOnPause, endDate,otherCol)
select isOnPause,null,otherCol from t 
where isOnPause 
and CAST(endDate AS CHAR(20))  like  '%10:00%' ; 

select * from t;

id | isOnPause | endDate             | otherCol
-: | --------: | :------------------ | :-------
 1 |         1 | 2022-03-29 10:00:00 | other 1 
 2 |         0 | 2022-03-29 10:00:00 | other 2 
 3 |         1 | 2022-03-29 11:00:00 | other 3 
 4 |         0 | 2022-03-29 11:00:00 | other 4 
 5 |         1 | null                | other 1

db<>fiddle here