kavita kavita - 2 months ago 13
MySQL Question

add values to newly added column of one existing table from column of another existing table

I am new to Mysql. I have created two tables Users and Administrations as follows:

CREATE TABLE IF NOT EXISTS Users (
FName VARCHAR(10) NOT NULL,
MI CHAR(1) NOT NULL,
LName VARCHAR(15) NOT NULL,
ID CHAR(11) NOT NULL,
GENDER CHAR(6) NOT NULL,
ADDRESS VARCHAR(15) NOT NULL,
DOB DATE NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS Administration (
ID CHAR(11) NOT NULL,
Position VARCHAR(15) NOT NULL DEFAULT 'NYA',
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES Users(ID)
);


Populated Administration table as Follows:

INSERT INTO Administration (ID)
SELECT ID FROM Users WHERE Address = 'Brooklyn';


Now I added one New column to Administration table:

ALTER TABLE administration
ADD LName VARCHAR(25) AFTER ID;


Now my goal is to Populate LName into Administration Table.
I achieved it as follows:

TRUNCATE Administration;
INSERT INTO Administration (ID,LName)
SELECT ID,LName FROM Users WHERE Address = 'Brooklyn' ;


My Question is Using Truncate is good idea ? or is there any other solution for this.

Answer

TRUNCATE is a fine idea. But you can also update the table:

UPDATE Administration a JOIN
       Users u
       ON a.id = u.id
    SET a.Lname = u.Lname
    WHERE u.Address = 'Brooklyn';

Note: The WHERE clause is not needed if id is unique in Users.

For large tables, it is normally faster to re-insert all the data rather than updating all rows.