Query to flatten schema I have the following tables and columns:

Users

id | other-non-important-columns  

Containers

id | Users_id  | other-non-important-columns


Content

id | Container_id | other-non-important-columns


This is a 3 level hierarchy, however I would like to get rid of the second tier (Containers), and associate Content directly with Users i.e.

Content

id | Users_id 


What query can I construct to populate the Content table with Users_id?



I am planning to keep Containers. Right now, content must belong to one container. The ultimate solution moving forward will be:


Content belongs to 1 user.
Content can belong to 0 or more containers


...but I'm trying to think through this one step at a time.

The reason I have the columns named as generic 'Id', is because this seems to be what a lot of ORM tools seem to want. I have tried a few ORMs. There are ways of course that you can override that behavior and specify your own primary key, but it seems like it was common/standard practice to just create every table with an auto-incrementing primary key named id.
If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick.  (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version).

/* add the Users_ID column to the table */
ALTER TABLE dbo.Content ADD Users_ID INT NULL 
    CONSTRAINT FK_Content_Users_ID 
    FOREIGN KEY (Users_ID) REFERENCES dbo.Users(ID);
GO

/* Update the Users_ID column */
UPDATE dbo.Content 
SET Users_ID = Containers.Users_ID
FROM dbo.Content 
    INNER JOIN dbo.Containers ON Content.Container_ID = Containers.ID
WHERE Content.Users_ID IS NULL;
GO

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE dbo.Content ALTER COLUMN Users_ID INT NOT NULL;


As an aside from the actual question at hand, I would recommend against naming three separate columns ID, since down the road it becomes a nightmare to quickly identify what the ID actually means.  If it is called UserID in the Users table, then call it UserID in the Content table as well.



The code - with minor changes - to work in MySQL:

/* add the Users_ID column to the table */
ALTER TABLE Content ADD Users_ID INT NULL, 
    ADD CONSTRAINT FK_Content_Users_ID 
    FOREIGN KEY (Users_ID) REFERENCES Users(ID);

/* Update the Users_ID column */
UPDATE Content 
    INNER JOIN Containers ON Content.Container_ID = Containers.ID
SET Content.Users_ID = Containers.Users_ID
WHERE Content.Users_ID IS NULL;

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE Content MODIFY COLUMN Users_ID INT NOT NULL;