CODEWITHSUNDEEP
mysqlsql
Brian Pipa
Brian Pipa

Reputation: 806

Populate new table with data from two other tables

I'm trying to migrate some data from two tables into a new table and having a hard time figuring out how to do it.

Data: 

table fh rows: A, B, C, X, Y
table fhgb rows: B, C
table gvsi (currently empty) rows: A, X, Y
A is unique, and B+C is unique

When done, table gvsi should contains all rows in fh where X=value and the row isn't already in fhgb.

This is a one-time data migration to gvsi so performance isn't a huge deal. fh has 36 million rows with 12 million that I care about (where X=value). fhgb has 10 million rows. I'm expecting gvsi to contain about 2 million rows (12mil-10mil) when done.

I've googled and tried lots of things but can't get anything to work (JOINs, subqueries, etc.) Can anyone help me figure out how to do this?

Upvotes: 0

Views: 2139

Answers (4)

GeorgeVremescu
GeorgeVremescu

Reputation: 1253

1 step

INSERT INTO gvsi 
SELECT fh.a, fh.x, fh.y FROM fh 
WHERE fh.x = value AND NOT EXISTS 
(SELECT 1 FROM fhgb WHERE fh.b = fhgb.b AND fh.c = fhgb.c);

2 steps: 

DELETE FROM fh WHERE EXISTS 
(SELECT 1 FROM fhgb WHERE fh.b = fhgb.b AND fh.c = fhgb.c);
INSERT INTO gvsi SELECT fh.a, fh.x, fh.y from fh WHERE fh.x = value;

The first one is safer because at the end, if you are not happy with the result, you can repeat the thing. It might be slower, however, due to the conditions.

Upvotes: 0

Brian Pipa
Brian Pipa

Reputation: 806

Our DB guy came up with the answer in 2 minutes:

INSERT INTO gvsi (A, B, X, Y)
SELECT fh.A, fh.B, fh.X, fh.Y
FROM
f_h fh
LEFT OUTER JOIN f_h_g_b fhgb ON fh.A=fhgb.A and fh.B=fhgb.B
WHERE fhgb.A IS NULL

That's not to say some of these answers won't work, but this one worked first try. Thanks for looking at it. I should try some of these other answers too and see if they work and if they work faster.

Upvotes: 0

AbdElrhman
AbdElrhman

Reputation: 52

well it looks easy and complicated when you do it with simple select insert statement and union the 2 tables in one step.

 insert Into 3d_Table ( column1, column2, column3) 
  select column1, column2, column3
   ( 
    select column1, column2, column3 
    from 2d_table
    union 
    select column1, column2, column3 
    from 2d_table
  )

if this help you, tried

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

INSERT INTO `gvsi` (A, X, Y)
SELECT A, X, Y
FROM fh 
WHERE X = 'value'
    AND ( fh.B NOT IN (SELECT DISTINCT B FROM fhgb)
        AND fh.C NOT IN (SELECT DISTINCT C FROM fhgb)
    );

That ought to do it.

Upvotes: 1

Related Questions