[Resolved] DELETE with SELECT subquery

Nate

I need to do:

DELETE FROM mytable WHERE id1=(SELECT id1,max(id2) FROM mytable WHERE ... GROUP BY id1)

It needs to delete where id1 = the id1 in the SELECT... It will perform the delete if i don't have max(id2) but i need max(id2) so i can grab a timestamp field from that SELECT query and delete according to it.

Lars_Berg

DELETE FROM mytable 
WHERE (id1,id2) in (SELECT id1,max(id2)
   FROM mytable 
 GROUP BY id1)

Nate

Lars, thanks but if i do that won't it delete where id1=id1 AND id2=max(id2)? I only want to delete where id1=id1.

Lars_Berg

Lars, thanks but if i do that won't it delete where id1=id1 AND id2=max(id2)?

Well, I thought that was you wanted but obviously I misunderstood.

Could you describe the table with some sample data and the desired result. Knowing which DBMS you are using may also be relevant.

Nate

Using MySQL

Let's say NOW() is 2003-12-01 12:55:00

table: `requests`
-------------------------
session_id     request_id     logged_date_time
1              1              2003-12-01 12:00:00
1              2              2003-12-01 12:01:00
1              3              2003-12-01 12:01:30
2              1              2003-12-01 12:01:04
2              2              2003-12-01 12:02:12

I need to SELECT all session_id's WHERE request_id is the MAX() request_id in the session AND logged_date_time is greater than 20 minutes ago and delete the ENTIRE session (not just the last request in the session).

session_id     request_id     logged_date_time
1              3              2003-12-01 12:01:30
2              2              2003-12-01 12:02:12

SELECT session_id,max(request_id)
FROM requests
WHERE logged_date_time<=SUBTIME(NOW(),"00:20:00")
GROUP BY session_id

This gives me desired results, however I don't know how to then DELETE according to the session_id's it gives me.

If logged_date_time of the last request_id of a session is > 20 minutes ago it is safe to delete where session_id = the session.

Nate

Perhaps using:

SELECT session_id, max( request_id )
FROM raw_requests
WHERE logged_date_time <= SUBTIME( NOW( ) , "00:20:00" )
GROUP BY session_id

and dumping those results into a temporary table and then deleting from requests based on the session_id's in that temp table would work? It just seems I shouldn't have to go that far though..

Lars_Berg

So all of the (5) records shown should be deleted?

delete from mytable as m
where requestid <= (select max(requestid) from mytable 
where m.sessionid = sessionid
and logged_date_time <= SUBTIME( NOW( ) , '00:20:00' )

Nate

Thanks Lars, i'm getting a mysql error though and im not sure why... Any idea?

DELETE FROM raw_requests AS m WHERE request_id <= ( SELECT max( request_id )
FROM raw_requests
WHERE m.session_id = session_id AND logged_date_time <= SUBTIME( NOW( ) , '00:20:00' ) )

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE request_id <= ( SELECT max( request_id )
FROM raw_reque...

MySQL 4.1.1-alpha-nt

Nate

It seems to be the AS m part... Because when i do (just as a test)

DELETE FROM raw_requests AS m WHERE request_id <=0

I get the same error. but when i do:

DELETE FROM raw_requests WHERE request_id <=0

it works fine.

Wierd?

chriskl

Last time I checked, no release version of MySQL had subqueries.