StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIn mySQL, can you update a newly created table with fields from existing tables?
text
Body
copied!<p>I am new to mySQL and would like some help with a query I'm running. I'd like to update a newly created table with fields from existing tables. Here is what I'm working with.</p> <pre><code>UPDATE NEW_TABLE new, OLD_TABLE_1 one, OLD_TABLE_2 two, OLD_TABLE_3 three SET new.COLUMN_1 = one.COLUMN_1, new.COLUMN_2 = one.COLUMN_2, new.COLUMN_3 = one.COLUMN_3, new.COLUMN_4 = two.COLUMN_1, new.COLUMN_5 = two.COLUMN_2, new.COLUMN_6 = three.COLUMN_1, new.COLUMN_7 = three.COLUMN_2 WHERE three.COLUMN_1 = two.COLUMN_1 AND three.COLUMN_2 = one.COLUMN_1; </code></pre> <p>My data is all HR data. <code>OLD_TABLE_1</code> is address information, <code>OLD_TABLE_2</code> is medical information, and <code>OLD_TABLE_3</code> is the original HR data. In the where clause, three.<code>COLUMN_1</code> has a relationship with two.<code>COLUMN_1</code>. Imagine that three.COLUMN_1 is the user's ID and it maps to a user's ID in two.<code>COLUMN_1</code>. Also imagine that three.<code>COLUMN_2</code> is a user's home phone number that maps to a user's home phone number in one.<code>COLUMN_1</code>. When the data is entered into the new table, I only want data for a unique individual. The reason for creating a new table is to optimize reporting. Drawing data from three tables is time consuming. I have large tables over 150GB in size. If I can load the data I need into a temp table, the query will run much quicker.</p> <p>Any help would be greatly appreciated. Thanks in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload