[FILTER ERROR]: You can't specify target table 'MT' for update in FROM clause

1

I have a query below

mutation updateTaskPosition (
  $tasksEquation: TaskUpdateByFilterInput!
){
  movedTasks: taskUpdateByFilter(
    data: $tasksEquation,
    filter: {
      parent: null
    }
  ) {
    items {
      id
    }
  }
}

that is causing this error message:

“You can’t specify target table ‘MT’ for update in FROM clause”

Any other filter works well, but this filter, as well as any other filter to the parent field throws that error. I am trying to filter Tasks that have no parent.

The relationship of the Task and Parent field is a one-to-many relationship from Parent to Task.

What could be the workaround for this?

2

Hello!

I’ll aswer you a few moments later. I’ve got your problem.

Lada Kokotova | Technical Support Engineer

3

I’ve tried to reproduce this mutation. It worked.

21
Снимок экрана 2021-09-16 в 22.14.21.jpg2516×908 184 KB

Could you send me you workspace ID, environment Name, $tasksEquation and time when did this situation happened? It will help me to resolve your issue.

Lada Kokotova | Technical Support Engineer

4

Hi! Here is my Workspace ID
cknjbrc7000j70cjwfsyk5442

Not sure what you meant by environment name, perhaps Tasks?

The equation is below:

"tasksEquation": {"position": {"add": 0}}

It would be good to note though that the data variable does not contribute to it failing. This equation still throws an error:

mutation updateTaskPosition {
  movedTasks: taskUpdateByFilter(
    data:{
      complete: {
      	set: false
      }
    },
    filter: {
      parent: null
    }
  ) {
    items {
      id
      position
    }
  }
}

I just tried it again now, so you can refer to the time of this reply

5

Thanks! I’ll check what’s wrong. I’ll let you know soon.

Lada Kokotova | Technical Support Engineer

6

This problem still exists for me.

7

Sorry about the wait. I remember about your issue, but there’re a lot of urgent tasks. I’ll give you a response as soon as possible.

Lada Kokotova | Technical Support Engineer

8

Hello again,

We have already created a task for this and it will be included in the next release.
You are getting this error for a limitation of MySQL engine, we can’t just reference the same table inside UPDATE statements, we need to wrap the reading table in a separate subquery.

Simplified example of current query:

UPDATE `Test` AS `MT` SET `MT`.`text` = 'Example')
WHERE NOT EXISTS (
  SELECT * FROM `Test` AS `RMT`
  INNER JOIN `TestTest` AS `SRT`ON `SRT`.`testBId` = `RMT`.`id`
  WHERE `SRT`.`testAId` = `MT`.`id` AND `RMT`.`deletedAt` = 0
)

We will let you know when the release will be deployed to production.
Thank you for the bug report.

Lada Kokotova | Technical Support Engineer

1 Like
9

Hello!

Today we made a release v0.0.242 with the fix that solves your problem.

Lada Kokotova | Technical Support Engineer