CODEWITHSUNDEEP
mysqlsql
user225269
user225269

Reputation: 10893

Using REPLACE INTO with multiple tables

I'm using the REPLACE query to replace the contents of one table with the other table. Here's my table:

table_one

table_two

Here's the query that I'm currently using:

REPLACE INTO tbl_two (id, fname)
SELECT id, name FROM tbl_one

How do I do it so that the existing values in the lname field doesn't become NULL?

Upvotes: 2

Views: 2049

Answers (4)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95741

Loosely speaking, the documented behavior of REPLACE is to insert (if the primary key doesn't exist in the target), or to delete and insert.

If the primary key exists in the target, that row will be deleted, and the REPLACE statement will act like a SQL INSERT statement. So all the values in the new row have to be supplied either a) by the REPLACE statement, or b) by default values in the table definition.

If you want some of the old values to persist, you have to select them from the target table, and supply those values as part of the REPLACE statement.

INSERT ... ON DUPLICATE KEY UPDATE might be a better choice.

Upvotes: 2

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

well, REPLACE INTO will delete and recreate a row when an existing row (identified by PK or UNIQUE) is found

you may rather use

INSERT INTO...
ON DUPLICATE KEY UPDATE

INSERT INTO tbl_two (id, fname)
SELECT t.id, t.name FROM tbl_one t
ON DUPLICATE KEY UPDATE id = t.id, fname = t.name

see SqlFiddle

Upvotes: 3

Hemant Nagar
Hemant Nagar

Reputation: 9

INSERT INTO tbl_two (id, fname)
SELECT id, ISNULL(name,'') FROM tbl_one

try above sql query. ISNULL function convert null value in ''(blank).

Upvotes: -1

iarchitect
iarchitect

Reputation: 1

You can use

REPLACE INTO table_two (id, fname, Lname) SELECT id, fname,"" FROM table_one

Try it out at http://sqlfiddle.com/#!2/67a0f/1 using the following scripts

CREATE TABLE table_one( ID INT NOT NULL, fname VARCHAR (20) NOT NULL, PRIMARY KEY (ID) );

CREATE TABLE table_two( ID INT NOT NULL, fname VARCHAR (20) NOT NULL, Lname VARCHAR (20) NOT NULL, PRIMARY KEY (ID) );

Upvotes: -1

Related Questions