Mysql – Move column to another table by reducing

database-designMySQLsyntax

I'm not very experienced with database so sorry for lacking correct terminology.

I have a structure similar to

Employee
-Id
-Location
-Name
-StoreId

Store
-Id
-Name

And I want to simplify/refactor so that the store has the location rather than the employee. And all employees should all have the same location if they have the same storeId.

How can I move location to Store while dropping location on Employee and setting it to the correct value?
If somehow the locations are mismatching, can I use the one with the most hits?

Something like

ALTER TABLE store
    add column location ???

alter table employee
    drop column location

Best Answer

You'll need to do it in several steps:

Add the column to the store (I'm not sure what the type is, maybe it's something else):
```
ALTER TABLE store ADD COLUMN location VARCHAR(50)
```
Populate it, based on the most prevalent location of the employees. I'm not entirely familiar with MySQL syntax but the query should look something like this:
```
UPDATE store SET location = locations_by_storeid.location
FROM store INNER JOIN 
(SELECT storeid, location,
  ROW_NUMBER() OVER(PARTITION_BY storeid ORDER BY COUNT(*) DESC) row_number
  FROM employee
  GROUP BY storeid, location) locations_by_storeid
WHERE store.id = locations_by_storeid.storeid
  AND locations_by_storeid.row_number = 1
```
The subquery (locations_by_storeid) lists all locations for employees on a certain storeid, and we take only the most frequent one (the one with row number 1) as per your requirement 2.
Now we can remove the location from the employee:
```
ALTER TABLE employee DROP COLUMN location
```

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Mysql – Inserting filtered data from one table to another

You can insert the result of a SELECT using INSERT ... SELECT syntax. To get rows from one table that do not have corresponding rows in another you can use the A not in B LEFT JOIN MySQL idiom or a similar one with NOT EXIST subquery (though the latter is less efficient).

Please give your table details for more specific query.