MySQL DELETE Query with derived table

Question

Good day all.

I am stuck on a MySql delete query.

Basically I need to delete the results of this SELECT query :

SELECT radcheck.* FROM
(SELECT
  permanent_users.realm,
  devices.name,
  devices.created,
  TimeDiff(Now(), devices.created) AS TimeDifference
FROM
  permanent_users
  INNER JOIN devices ON devices.permanent_user_id = permanent_users.id
WHERE
  TimeDiff(Now(), devices.created) > '01:30') MySubQuery
  INNER JOIN radcheck ON MySubQuery.name = radcheck.username

Any help would be greatly apreciated!

Thank you

ScaisEdge · Accepted Answer · 2017-08-23T16:50:33.110

0

should be

  delete radcheck from radcheck
  inner join  (SELECT
      permanent_users.realm,
      devices.name,
      devices.created,
      TimeDiff(Now(), devices.created) AS TimeDifference
    FROM
      permanent_users
      INNER JOIN devices ON devices.permanent_user_id = permanent_users.id
    WHERE
      TimeDiff(Now(), devices.created) > '01:30') MySubQuery on MySubQuery.name = radcheck.username

edited Aug 23 '17 at 16:50

answered Aug 22 '17 at 16:22

ScaisEdge

131,976
10
91
107

HI, Thak you very much for the prompt response. i get error "ERROR 1064 (42000): 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 'inner join (SELECT permanent_users.realm,devices.name,devices.created,TimeDiff(N' at line 1" – XeWonder Aug 23 '17 at 06:08
delete radcheck from radcheck inner join (SELECT permanent_users.realm, devices.name, devices.created, TimeDiff(Now(), devices.created) AS TimeDifference FROM permanent_users INNER JOIN devices ON devices.permanent_user_id = permanent_users.id WHERE TimeDiff(Now(), devices.created) > '00:05') MySubQuery on MySubQuery.name = radcheck.username; This works – XeWonder Aug 23 '17 at 08:23
@XeWonder a part for the change of 00:05 respect to 01:30 .. i don't see difference so .. if my answer is right or lead you to the right solution please mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – ScaisEdge Aug 23 '17 at 16:40
Hi @scaisEdge. Up untill 40 minutes ago your answer was giving the error mentioned. Now that you have edited it with the additional "from radcheck" like mine (and works) I accept. You have been a great help and thank you so much!! – XeWonder Aug 23 '17 at 17:39
yes .. correct the update .(for mysql) .need table name and from .. i was out for others job and when i have seen you comment i have update with the table name in right place .. – ScaisEdge Aug 23 '17 at 17:50

score 0 · Answer 2 · answered Aug 23 '17 at 08:25

Following on frmo the hint of scaisEdge, this works

THANK YOU!!

delete radcheck from radcheck
  inner join  (SELECT
     permanent_users.realm,
     devices.name,
     devices.created,
     TimeDiff(Now(), devices.created) AS TimeDifference
  FROM
     permanent_users
     INNER JOIN devices ON devices.permanent_user_id = permanent_users.id
  WHERE
     TimeDiff(Now(), devices.created) > '00:05') MySubQuery on MySubQuery.name = radcheck.username;

MySQL DELETE Query with derived table

2 Answers2