Mysql – How to automate insertion of new column in Mysql table

MySQL

I have a legacy database, and several tables need a new column added that will act as the primary key for the table. The trick is there are several tens of thousands of existing rows, and the data for the new column will be the concatenation of two field values within the table.

Specifically, the new column can be temporarily created via 

SELECT *, CONCAT(ContractNum, '-', PlanId) AS ConPlanId FROM ContractPlans

Using a batch script, how can I populate the exising column fields which are currently null?

Best Answer

primary key...will be the concatenation of two field values within the table.

no NO NO!!!!!

This breaks all the rules of database normalization (well, rule 1 and rule 2 actually) and it's totally unnecessary! It uses more storage, makes your queries less efficient, and may cause bugs in your processing.

Use the existing columns as the primary key.

Presumably it doesn't have a primary key declared already.

Check that you've got no duplicates in the table:

SELECT ContractNum, PlanId, COUNT(*) 
FROM ContractPlans
GROUP BY ContractNum, PlanId
HAVING COUNT(*)>1;

Fix the data if you need to, then

ALTER TABLE ContractPlans
ADD PRIMARY KEY (ContractNum, PlanId);
Related Question