StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you are deleting rows, I am assuming they are not here in table A, so what would you be sending them to table B?</p> <p>If by deleting you mean 'marked as deleted', then simply filter by that condition in your <code>REPLACE</code> statement.</p> <blockquote> <p>REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.</p> <p>REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.</p> <p>Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.</p> </blockquote> <p>After reading your comment, I understood your problem thusly:</p> <p>If A has 10 and B has 0, at first replication B will have 10 and A will have 0 (because you will delete them).</p> <p>At next replication, A will have 5 and these won't be present in B, so you simply copy all from A to B, so B has 15 (again A will be deleted).</p> <p>If however, you only want to replicate a subset of rows from A, then you need to apply that filter first (for <code>REPLACE</code>).</p> <p><code>REPLACE</code> will do this for you - except delete the rows from you source table, which you'll have to do manually. It will keep B updated always.</p> <pre><code>mysql> create table A (id INT AUTO_INCREMENT PRIMARY KEY, foo VARCHAR(12)); Query OK, 0 rows affected (0.05 sec) mysql> create table B (id INT AUTO_INCREMENT PRIMARY KEY, foo VARCHAR(12)); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO A VALUES ('','One'),('','Two'); Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> REPLACE INTO B SELECT * FROM A; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM B; +----+------+ | id | foo | +----+------+ | 1 | One | | 2 | Two | +----+------+ 2 rows in set (0.00 sec) mysql> UPDATE A SET `foo` = 'One-Up' WHERE `id` = 1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM A; +----+--------+ | id | foo | +----+--------+ | 1 | One-Up | | 2 | Two | +----+--------+ 2 rows in set (0.00 sec) mysql> REPLACE INTO B SELECT * FROM A; Query OK, 4 rows affected (0.00 sec) Records: 2 Duplicates: 2 Warnings: 0 mysql> SELECT * FROM B; +----+--------+ | id | foo | +----+--------+ | 1 | One-Up | | 2 | Two | +----+--------+ 2 rows in set (0.00 sec) mysql> DELETE FROM A where id = 1; Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO B SELECT * FROM A; Query OK, 2 rows affected (0.00 sec) Records: 1 Duplicates: 1 Warnings: 0 mysql> SELECT * FROM A; +----+------+ | id | foo | +----+------+ | 2 | Two | +----+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM B; +----+--------+ | id | foo | +----+--------+ | 1 | One-Up | | 2 | Two | +----+--------+ 2 rows in set (0.00 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload