Search code examples
mysqlderived-table

MySQL DELETE Query with derived table

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

Solution

  • 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