将REPLACE INTO与多个表一起使用

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

我正在使用REPLACE查询将一个表的内容替换为另一个表。这是我的表:

table_one

id
name

table_two

id
fname
lname

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?

我该怎么做才能使lname字段中的现有值不变为NULL?

4 个解决方案

#1

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

好吧,当找到现有行(由PK或UNIQUE标识)时,REPLACE INTO将删除并重新创建一行

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

#2

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.

简而言之,REPLACE的记录行为是插入(如果目标中不存在主键),或者删除和插入。

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.

如果主键存在于目标中,则该行将被删除,REPLACE语句将充当SQL INSERT语句。因此,必须通过REPLACE语句提供新行中的所有值,或者b)通过表定义中的默认值提供。

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.

如果希望某些旧值保持不变,则必须从目标表中选择它们,并将这些值作为REPLACE语句的一部分提供。

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

INSERT ... ON DUPLICATE KEY UPDATE可能是更好的选择。

#3

-1

You can use

您可以使用

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

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

使用以下脚本在http://sqlfiddle.com/#!2/67a0f/1上试一试

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

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) );

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

#4

-1

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

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

尝试上面的SQL查询。 ISNULL函数在''(空白)中转换空值。

#1