MySQL – Replace Rows in One Table with Rows from Another Table

MySQLphpmyadminrestore

I have two tables and a set of primary key values. I want to replace all rows of the first table whose primary keys are in this set with the corresponding (having the same primary key) rows of the second table.

(The two tables have the same structure.)

How to do this with MySQL and PHPMyAdmin or (worse) command line?

You can assume that both tables have a row for every value of the primary keys in the set.

Best Answer

UPDATE table1, table2
SET table1.field1 = table2.field1,
    table1.field2 = table2.field2,
--  .....   
    table1.fieldN = table2.fieldN
WHERE table1.id = table2.id
  AND table1.id IN ('id1', 'id2', /* ... */ 'idN' )

Or you can use

REPLACE table1
SELECT * 
FROM table2
WHERE table1.id IN ('id1', 'id2', /* ... */ 'idN' )

but it can give undesired interferention if there is any additional unique index except primary one.

ABOUT YOUR QUESTION

Your first query

SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;

resembles a natural join where nothing matches on the cardinal table, so the count 2677513 is correct.

Your second query

SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;

is little more explicit, but the c.id=null makes it fail, thus getting 0 as a count.

MySQL Auto-Increment Issue – Table Shows ID (Auto-Increment) as 0 in All Rows

The problem is that you can not create AUTO_INCREMENT without a key so try something like this:

CREATE TABLE `project_application` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`project_title` varchar(255) NOT NULL,
`project_id` int(11) NOT NULL,
`team_leader` varchar(255) NOT NULL,
`team_member` text,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE     CURRENT_TIMESTAMP,
`createt_et` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

Hope this helps

Best Answer

Related Solutions

MySQL – Select Records in One Table but Not in Another Without Matching Primary Key

ABOUT YOUR QUESTION

MySQL Auto-Increment Issue – Table Shows ID (Auto-Increment) as 0 in All Rows

Related Question