Menu
I have a table in my DB, in which every row has a parent id which is the id of another row in the table (the table represents a tree-like structure). I would like to empty the table. But when I perform
DELETE FROM table_name WHERE true;
I get an error (foreign key constraint). How do I empty the table anyway?
Clarification: I want to delete the whole table's contents, not the tables themselves.
817
To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
To do that, select the table you wish to delete records and click "SQL" tab. Delete the existing query in the query-box and click "DELETE" button located at the bottom. You will see query-box inserted with DELETE query as ' DELETE FROM `wp_comments` WHERE 1' . Click Go and you are done.
TRUNCATE TABLE tablename; This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query. The number of deleted rows for MyISAM tables returned is zero; for INNODB it returns the actual number deleted.
AS ( subquery ) Select col1, col2, .. from cte_name; cte_name: Name given to recursive subquery written in subquery block. col1, col2, ... colN: The name given to columns generated by subquery. subquery: A MySql query that refer to itself using cte_name as its own name.
When you create your foreign key relationships, you need to specify on delete cascade.
EDIT: There's a pretty good reference right here: http://en.wikipedia.org/wiki/Foreign_key
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
