Php和MySql - 将数据从一个表复制到另一个表

I just want to know how if it is possible to copy data from one table column to another in the same database? The code below is not working with my version of MySQL but it should be working.

我只想知道如何在同一个数据库中将数据从一个表列复制到另一个表列?下面的代码不适用于我的MySQL版本,但它应该正常工作。

UPDATE `table1` SET `table1.column1` = 
(SELECT `table2.column2` FROM table2 WHERE `table1.city` = 'table2.city') 
WHERE `listing` ='5'

Do you have some idea ? Thanks !

你有什么想法吗?谢谢 !

Guys, As I thought solution is possible with php script and solution for this problem is to make a short script which is gonna do all this up with while loop :)

伙计们,因为我认为解决方案是可能的PHP脚本和这个问题的解决方案是制作一个简短的脚本,这将完成所有这一切与循环:)

Thanks everybody on joining this discussions !

感谢大家加入讨论!

The solution was in `` marks between them should be column names, this works nice, enjoy !

解决方案是``它们之间的标记应该是列名,这很好用,享受!

4 个解决方案

#1

Yes you can do with INSERT ... SELECT Syntax like:

是的,您可以使用INSERT ... SELECT语法,如:

INSERT INTO database2.table1 (field1,field3,field9)
SELECT table2.field3,table2.field1,table2.field4
FROM table2;

Check Mysql

#2

It reads like you're attempting to perform an update, not an insert.

它读起来就像你试图执行更新,而不是插入。

UPDATE table1 AS t1 INNER JOIN table2 AS t2 ON t1.city=t2.city 
SET t1.column1 = t2.column2
WHERE t1.listing ='5'

In the case that you do need to copy data from one table to the other then you need to follow this syntax:

如果您确实需要将数据从一个表复制到另一个表,则需要遵循以下语法:

INSERT INTO tablename (field1,field2,field3) (SELECT field1,field2,field3 FROM another_table);

#3

Try this.

UPDATE table1, (
    SELECT table2.city, table2.column2 
    FROM table2  ) table2
SET table1.column1 = table2.column2
WHERE table1.city = table2.city
AND listing ='5'

#4

Try with

INSERT INTO table2
   AS (SELECT *
FROM table1
WHERE table1.city = table2.city AND 
listing = 5);

as per column try like

按照栏目试试

INSERT INTO table2 (columnname1) values (SELECT columnname1 FROM table1 
WHERE table1.city = table2.city AND 
listing = 5);

#1