Case of use
We have a log table without a PK with two columns (date, event);
You inserted a row. Then you want to undo the inserted record.
Is there a way to delete that row (without deleting other rows with the same data), that doesn't use postgres only capabilities?
I want a solution that works in another database (it doesn't need to be totally standard, it can be only in just one database: SqLite, Oracle, MySql or SQLServer).
Example:
create table the_log(
date date,
event_id integer
);
insert into the_log(date, event_id) values ('2019-09-21',1),('2019-09-21',1);
select * from the_log;
My atempts:
delete from the_log where row_number() over ()=1;
delete from the_log limit 1;
with the_log_2 as (select *, row_number() over () as version from prueba_sin_clave)
delete from the_log_2 where version=1;
I supouse that the answer is No. I want to know if I am wrong or in what documentation I read that I am right.
Best Answer
CAUTION : This can make you lose data, so consider using transaction block.
I think this is the more generic solution:
If you consider that the line you want to delete is:
then if you have one column that has a unique value...
If you do not have a unique column, but you have two columns that combined generates a unique value:
Where any_the_log_column is a column with a unique value in this table.
This will probably delete the first line of the log table in any dbms - if data in column is not repeated, but remember that tables sometimes is not selected ordered in some databases so you must have a id in this table...
Consider creating a id column and increasing it by the numbers of the lines in the table, based on date or something if you don't want to get hurt in the future...