Can SQL Move Data Between Tables Based on a Query?

Moving Data from One Table to Another Based on a Query

Q: Can SQL be used to selectively move table data based on a query?

A: Yes, it is possible to move rows between two tables based on a specific query, effectively transferring matching rows to the destination table while removing them from the source table.

Method:

1. Insert into Destination Table: Insert matching rows from the source table into the destination table using the following syntax:

   INSERT INTO Table2 (columns)
   SELECT columns
   FROM Table1
   WHERE condition;

2. Delete from Source Table: After inserting matching rows, delete them from the source table using the following syntax:

   DELETE FROM Table1
   WHERE condition;

3. Transaction: To ensure data consistency, these two statements should be executed within a single transaction using the following syntax:

   BEGIN TRANSACTION;
   
   [Insert statement]
   [Delete statement]
   
   COMMIT;

This process effectively moves matching rows from Table1 to Table2, leaving no duplicates in the source table.

The above is the detailed content of Can SQL Move Data Between Tables Based on a Query?. For more information, please follow other related articles on the PHP Chinese website!