Mysql method to copy a column of data in one table to a column in another table

mysql copy one table column to another table

Sometimes, we need to copy a whole column of data from a field to another new field. This is very simple. The SQL can be written like this:

UPDATE tb_1 SET content_target = content_source;

The general way of writing is as follows:

Update {your_table} set {source_field} = {object_field} WHERE cause

It is better to have tools such as Navicat, which can directly select a column of data and copy and paste it into the column you need. If it is the same table, there is no problem. If it is a new table, please keep the number of rows consistent. If the number of rows is inconsistent, you can create a new table and copy the columns into it, so that the number of ids will remain consistent.

Sometimes these MySQL interface tools will report errors, in which case it is better to use the command line. For example, to copy the data of a table field to another table field, you can write:

UPDATE tb_1 INNER JOIN tb_2 ON tb_1.tid = tb_2.tid
SET tb_1.tcontent = tb_2.tcontent

The following is a practical example, which writes the link of the static page generated by PHPCMS into the url field in the phpcms_content table:

First, piece together the required URL field column.

SELECT CONCAT(FROM_UNIXTIME(inputtime,'%Y/%m%d'), '/', contentid, '.html') AS dt FROM phpcms_content ORDER BY contentid DESC

Then in the query editor (navicat), copy the entire paragraph to the url column in the phpcms_content table.

Example of copying fields between different tables:

Requirement: Copy the name field of the student table to the name field of the student_rec table

student table

student_rec table

SQL implementation:

UPDATE student_rec INNER JOIN student ON student.id = student_rec.student_id 
SET student_rec.`name` = student.`name`

result:

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links