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;

StackExchange Database Administrators Q#102310, answer score: 2