CODEWITHSUNDEEP
sqlpostgresqlsql-delete
B3n Sp1ndl3r
B3n Sp1ndl3r

Reputation: 35

Delete with aliases on two tables on the same query[Postgres 9.5]

I'm not sure what is wrong here: 

DELETE t1 FROM modified_client_config AS t1, modified_client_config AS t2
WHERE t1.Date < t2.Date AND t1.Client = t2.Client AND t1.Server = t2.Server;

I search before asking the question and found there was a rule when deleting with aliases: 

DELETE f FROM dbo.foods AS f WHERE f.name IN (...)

So, is that possible to use this syntax with to aliases?

I know it is the same table with the two alias but I imagine my colleague wrote this to delete only when date1 < date2.

Upvotes: 0

Views: 1894

Answers (1)

user330315
user330315

Reputation:

It seems you only want to retain the latest row for each combination of client and server.

You can do that with a co-related sub-select:

delete from modified_client_config as t1
where exists (select * 
              from modified_client_config as t2
              where t2.date > t1.date
                and t2.client = t1.client 
                and t2.server = t1.server);

Online example: http://rextester.com/LOAN66059

Upvotes: 3

Related Questions