How to use Temporary Table In MariaDB
We’ll assume that the table has been restored to its original state (with the duplicates).
Create a temporary table with distinct records and then overwrite the original table with the temporary one.
-- Create a temporary table with distinct rows
CREATE TABLE temp_states AS SELECT DISTINCT * FROM states;
-- Drop the original states table
DROP TABLE states;
-- Rename the temporary table to replace the original states table
ALTER TABLE temp_states RENAME TO states;
Output:
Duplicates Removed
Explanation:
- CREATE TABLE temp_table AS SELECT DISTINCT * FROM states;: This statement creates a new table temp_table with the distinct rows from the states table. All columns and their data types will be copied from the original states table.
- DROP TABLE states;: This statement removes the original states table from the database. All data and table structure will be permanently deleted.
- ALTER TABLE temp_table RENAME TO states;: This statement renames the newly created temp_table to states, effectively replacing the original table with the deduplicated data.
- After executing these SQL statements, the states table will contain only distinct rows, and the original table will be replaced. Only unique values are there.
How to Delete Duplicate Rows in MariaDB
Duplicate rows in a database can lead to data inconsistencies and inefficiencies. In MariaDB, we can remove duplicate rows using various methods to ensure data integrity and optimize database performance.
In this article, We will explore different techniques to identify and delete duplicate rows in MariaDB with the help of examples and so on.
Contact Us