Mysql - Move Rows from One Table to Another

mysql - move rows from one table to another

A simple INSERT INTO SELECT statement:

INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom';

---

DELETE FROM customer_table WHERE person_name = 'tom';

mysql move row between tables

You're going to need at least 2 queries:

INSERT INTO table2 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table 1 WHERE <insert_where_clause_here>

DELETE FROM table1 WHERE <your_where_clause>

I see no shorter way of doing this using MySQL

Mysql - move from one table to another if row doesnt exist in new table

Could you try the folllowing query:

INSERT INTO logins(firstname, lastname, email, departmentid)
SELECT firstname, lastname, email, departmentid 
FROM temp_uploads 
WHERE tempuploadid = ?
AND NOT EXISTS (
  SELECT * FROM logins WHERE email = temp_uploads.email
);

MySQL - Move row from one table to another table but use a new ID

Try this:

INSERT into 
    new_table (
        `column1`, `column2` -- and all other columns except id
    )
(SELECT `column1`, `column2` -- and all other columns in old_table except id
FROM
    old_table
-- if you have WHERE clause do it here...
)    

How to atomically move rows from one table to another?

A possible way to avoid all those problems, and to also stay fast, would be to use two data_buffer tables (let's call them data_buffer1 and data_buffer2); while the collection processes insert into data_buffer2, you can do the insert and delete on data_buffer2; than you switch, so collected data goes into data_buffer2, while data is inserted+deleted from data_buffer1 into data.

mysql - move rows from one table to another or update

Try this:

EXPLAIN SELECT * FROM submitdata;

Perhaps it will tell you which fields you can give an INDEX to (this could speed up the query time drastically). Then, if you're not going to move records whose backlink and affiliateid fields are empty, then you can reduce the result set from the query by adding a WHERE clause.

$result = mysql_query('SELECT * FROM submitdata WHERE backlink <> "" AND affiliateid <> ""');
if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        $newResult = mysql_query(sprintf('SELECT name,progid FROM maindata WHERE progid = %d', $row['progid']));
        $row2 = mysql_fetch_row($newResult);
        if ($row2) {
            // do UPDATE query
        } else {
            // do INSERT query
        }
    }
}

MySQL move random row from one table to another

Thank you to all, but I have come up to do it this way:

$accCount = mysqli_query($connect, "SELECT * FROM AllRows");
$accCount = mysqli_num_rows($accCount);
$RandAcc = rand(0, $accCount);
$result = mysqli_query($connect, "SELECT * FROM `AllRows`");
$ReplaceRandAcc = mysqli_result($result, $RandAcc);

mysqli_query($connect, "INSERT INTO `ShowRow` SELECT * FROM `AllRows` WHERE login='".$ReplaceRandAcc."'");
mysqli_query($connect, "DELETE FROM `AllRows` WHERE login='".$ReplaceRandAcc."'");

function mysqli_result($res, $row, $field=0) {
    $res->data_seek($row);
    $datarow = $res->fetch_array();
    return $datarow[$field];
} 

---

Related Topics