Multi-table deletions

Help Query Tuning
1

Hi all,

Currently trying to purge old data with the following query:

delete from player_sessions
left join servers on player_sessions.server_id = servers.id
where servers.deleted_at is not null

When doing so, I’m provided with the following error:

ERROR 1149 ER_SYNTAX_ERROR: Target of Multi-Table Delete ambiguous
2

I don’t think we support multi-table deletions. I found this post on Stack Overflow: mysql - memsql multi-table delete, is it possible? - Stack Overflow

Have you tried something like this?
delete from player_sessions
where exists (select * from servers
where player_sessions.server_id = servers.id and servers.deleted_at is not null)

1 Like
3

Hi Charlie!

Our engineering team was able to recreate the error. SingleStore DB Cloud. Per the docs

Although DELETE supports referencing multiple tables using either joins or subqueries, SingleStoreDB Cloud only supports deleting from one table in a DELETE statement.

SingleStoreDB Cloud · SingleStore Documentation,
To give this a go, you’ll have to name the table that you want to perform the delete against like the following:

delete <table_w_records_to_delete> from player_sessions left join servers on player_sessions.server_id = servers.id where servers.deleted_at is not null

Hope this works for you. Keep us posted. Thank you!

4

@heychazza I think you want this:

delete player_sessions from player_sessions left join servers on player_sessions.server_id = servers.id where servers.deleted_at is not null;

That runs without error.

The general syntax is the second one listed here: DELETE · SingleStore Documentation

1 Like