![]() PREPARE drop_statement FROM 'DELETE FROM drop_statement USING PREPARE drop_statement ![]() If you want to use only MySQL for that, you can think of more advanced query, such as this: SET = (SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%') More shell oriented example would be: echo "SHOW TABLES LIKE 'table%'" | mysql | tail -n +2 | xargs -L1 -I% echo "DELETE FROM % WHERE user_id=123 " | mysql -v Then pipe it (in shell) into mysql command for execution.įor example it'll generate something like: DELETE FROM table1 WHERE user_id=123 However if all yours tables starts with certain name, then this query will generate query which would do that task: SELECT CONCAT('DELETE FROM ', GROUP_CONCAT(TABLE_NAME SEPARATOR ' WHERE user_id=123 DELETE FROM '), 'FROM table1 ' ) AS statement FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%' The column from_table is set by the stack building query, and is set to whatever the originating table was.Normally you can't DELETE from multiple tables at once, unless you'll use JOINs as shown in other answers. The $sql_stacks_ready variable contains my (semi) complex query that creates the ordered stack (and hence the need for all those aliases in the LOCK TABLES). I do not have it using transactions yet, since I still have yet to learn that. This is just simplified out from the original perl/dbi code, which does some logic to check each statement for failure. INSERT INTO main (id, title, added_on, date) SELECT id, title, added_on, date FROM stacks_tmp ĭELETE FROM main_date WHERE (id) IN (SELECT id FROM stacks_tmp WHERE from_table = 'date') ĭELETE FROM main_weekday WHERE (id) IN (SELECT id FROM stacks_tmp WHERE from_table = 'weekday') SELECT COUNT(*) FROM stacks_tmp ORDER BY date No temp table is needed and IO will be limited on tempdb.įor those curious, this is how I solved it, thanks to tip: LOCK TABLESĬREATE TEMPORARY TABLE IF NOT EXISTS stacks_tmp AS ( $sql_stacks_ready ) It simply inserts somewhere else the output of what has just been deleted. OUTPUT deleted.id, deleted.name, deleted.level INTO table2 ![]() = FALSE before the delete statement => delete and copy dataĪlthough the question is not about this RDBMS, with SQL Server I would rather use the OUTPUT clause: DELETE FROM t.Row with level=2 is not here anymore (See sample sqlfiddle). rows with 2 are not inserted into table2 (variable set to FALSE). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |