Katzenliebe Katzenliebe - 4 months ago 10
MySQL Question

Inserting Columns Without Duplicated Null Data in MySQL?

i don't know mysql very much. And i've problem about that. I've a database and it's 20 GB. I want to combine 4 columns and then move the combined column to the new one. But the problem is duplicated data in the table.

For example i wanna combine;

Column1(Not Null),
Column2(Some of them null, some of them not null),
Column3(Not Null),
Column4(Some of them null, some of them not null).


And my new column, which i want to move my combined columns, is fully empty. After my longly research, at last i find this code on dev.mysql.com

INSERT INTO my_table (new_content)
SELECT Column1
FROM my_table WHERE my_table > 0;


As a result, it moved Column1 to the new_content. But my the other 20 columns were duplicated too, as empty fields. How can i make it in an easy way?

Sorry for my bad English. Thanks in advance.

Answer

Create the column in already existing table:

ALTER TABLE my_table ADD COLUMN new_content VARCHAR(55);

Update table and concate all the columns to the newly created column:

UPDATE my_table SET new_content = CONCAT(Column1, Column2);

Create a trigger for all inserting values in future as well:

CREATE TRIGGER insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
SET new.new_content = CONCAT(Column1, Column2);

You can also create a trigger for UPDATE:

CREATE TRIGGER 
BEFORE UPDATE ON my_table
FOR EACH ROW
SET new.new_content = CONCAT(Column1, Column2);