I want to delete the second row in Table 1. After deleting the row, I want to save it as a new table as Table 2. I want Table 2 to be my final goal. What other statements do I need to put for my code?
Also, for my current table, the time is set as 0:00:00. I am aware that SQLite's time format is 00:00:00. I tried using time(), but it did not work as it did not delete the second row. Do I need to change the time format here?
Table 1:
| ID | BEFORE_DATE | BEFORE_TIME | BEFORE_DATE | AFTER_TIME |
|---|---|---|---|---|
| 9 | 2020-01-09 | 0:00:00 | 2020-01-09 | 2:43:11 |
| 7 | 2020-09-09 | 16:09:23 | 2020-09-09 | 13:00:00 |
My goal:
Table 2
| ID | BEFORE_DATE | BEFORE_TIME | AFTER_DATE | AFTER_TIME |
|---|---|---|---|---|
| 9 | 2020-01-09 | 0:00:00 | 2020-01-09 | 2:43:11 |
Code:
CREATE TABLE table2 AS
from table1
SELECT *
FROM table1
WHERE BEFORE_DATE=AFTER_DATE AND TIME(BEFORE_TIME) < TIME(AFTER_TIME);
CodePudding user response：
i think right syntax is like this , also you can combine date time columns to cover all the possible wrong after date/times:
create table table2
as
select *
from table1
where datetime(BEFORE_DATE || ' ' || BEFORE_TIME) < datetime(AFTER_DATE || ' ' || AFTER_TIME) ;
if you want to delete from table1, you can do this:
delete from table1
where datetime(BEFORE_DATE || ' ' || BEFORE_TIME) >= datetime(AFTER_DATE || ' ' || AFTER_TIME) ;
db<>fiddle here