Move column to another table by reducing?

Question

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.

1. How can I move location to Store while dropping location on Employee and setting it to the correct value?
2. 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

Answer 1

You'll need to do it in several steps:

1. 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)
   

2. 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.

3. Now we can remove the location from the employee:

   ALTER TABLE employee DROP COLUMN location