Re: Tricky SQL - assistance appreicated. DDL and DML supplied.

>> matt=# DELETE from ex where ex_id not in (SELECT t1.ex_id FROM ex t1 join (SELECT c_id, guid, max(ts) mts from ex
groupby c_id, guid) as t2 on t1.c_id = t2.c_id AND t1.guid = t2.guid AND t1.ts = t2.mts); 
>> DELETE 4
>> matt=# SELECT * from ex;
>>  ex_id | c_id | guid | supplier |         ts
>> -------+------+------+----------+---------------------
>>      3 |    1 | xxxx |       50 | 2016-07-15 22:05:01
>>      4 |    1 | xxxx |       50 | 2016-07-15 22:05:01
>>      7 |    2 | yyyy |       71 | 2016-07-17 22:05:01
>>      8 |    2 | yyyy |       74 | 2016-07-17 22:05:01
>>      9 |    3 | zzzz |       60 | 2016-07-01 22:05:01
>>     10 |    4 | aaaa |       61 | 2016-07-01 22:05:01
>> (6 rows)



Thanks for that - it was what I was looking for.


>
> I believe this solution should work with mysql as well, although I've only done some light testing



Nope! :-) When one runs this query and ones like it in MySQL, it's charming
and quaint (ahem....) dialect of SQL produces the error:

ERROR 1093 (HY000): You can't specify target table 'ex' for update in
FROM clause

So, you have to add a level of nesting to your query to SELECT ex_id
FROM your derived table!
<shakes head.... how on earth did MySQL become more popular than PostgreSQL?>

As it was put so aptly here
(http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause),
"Sometimes I wonder what drugs the MySQL devs are on..." (found while
searching
for a solution - also to be found in that thread).

Thanks again.



--

Pál Teleki