delete with group by

[pankajdurve]

How to delete bus service no where sync not update column not in max(sync_last_update)
i write following query
but it gives error

delete from bus_departute where bus_servic_no not in(select max(sync_last_update) group by bus_service_no)

  bus_service_no	bus_depart_time	  sync_last_update



    1       0000-00-00 00:00:00	2014-02-22 17:07:53
2	2014-02-22 17:30:32	2014-02-22 17:30:32
3	2014-02-22 17:30:32	2014-02-22 17:30:32
2	2014-02-22 17:30:39	2014-02-22 17:30:39
3	2014-02-22 17:30:39	2014-02-22 17:30:39
2	2014-02-22 17:30:47	2014-02-22 17:30:47
3	2014-02-22 17:30:47	2014-02-22 17:30:47
2	0000-00-00 00:00:00	2014-02-22 17:07:53
2	0000-00-00 00:00:00	2014-02-22 17:07:53
   1        0000-00-00 00:00:00	 2014-02-22 17:07:53

[Topcoder]

That’s an invalid query syntax… You are trying to do a select, without a from, you can’t do that.

[php]select max(sync_last_update) group by bus_service_no[/php]

The proper syntax will be

[php]select max(sync_last_update) from bus_departute group by bus_service_no[/php]

But even with the proper syntax the bus_servic_no will never match the sync_last_update, so your logic is faulty.