MySQL – How to Copy Text from One Table to Another If Target Column Is Empty

copyinsertMySQLtable

I have two tables in a database and I wish to copy text from one column of the table to another column in another table.

Lets say I have two tables called 'articles' and 'info'. I have to copy articles.article column text into info.description for the same articles, which can be identified by a so called 'Codename' and not ID. Preferably IF the column info.description is empty. I intend to use WHERE category = 4 to limit myself to only some articles, but that shouldn't be a problem and if the IF statement is correct even unnecessary.

Tables layout (Not whole, but enough for here):

Articles (ID, Codename (VARCHAR), Article (VARCHAR)) 
Info (ID2, Codename (VARCHAR), Description (VARCHAR))

So basically, I would like to give articles with no description a basic description with their name and some data, that is written beside the name.

Best Answer

Use a UPDATE FROM syntax:

UPDATE Info
SET Description = (Select Article
                   FROM Articles 
                   WHERE Info.Codename = Articles.Codename)
WHERE Description IS NULL;

http://rextester.com/EYIG7377

APPROACH #1

In the mysql client, run the following

USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

APPROACH #2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #3

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #4

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

APPROACH #5

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}

ANALYSIS

APPROACH #1 is the easiest in terms of steps, but requires pushing 40 million rows into one transaction. This will be the most taxing on the InnoDB Storage Engine.
For the other approaches, mysqldump will send 40 million row in chucks of thousands of rows
- APPROACH #2 and APPROACH #3 will mysqldump the table into the test database. After creating the table in the test database, it is subsequently renamed and moved into the original database
- APPROACH #4 and APPROACH #5 rename the table using sed against the stream coming from the mysqldump as it echoes the INSERT commands
- APPROACH #2 and APPROACH #4 use pipes instead of an output file
- APPROACH #3 and APPROACH #5 use an outpuit file for subsequent reload

If you want to copy mydb.mytb to an already existing table mydb.mytbcopy, and the two tables have identical structures:

APPROACH #6

INSERT INTO mytbcopy SELECT * FROM mytb;

Like #APPROACH 1, #APPROACH 6 would have a single transaction of 40 million rows

APPROACH #7

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

This approaches does not drop the table. It simply generates the INSERTs

EPILOGUE

I cannot give you a time estimate since I do not know the make up of the DB Server, table structure, index layout, and things like these.

GIVE IT A TRY !!!

Database Design – Best Logical Structure for Different Entity Categories

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.