CODEWITHSUNDEEP
mysql
Plummer
Plummer

Reputation: 6688

MYSQL INSERT getting data from INNER JOIN

I have two table (one small, one huge) that use the same unique key, but not the same columns.

I need to bring two columns from the big table into the small table but only for the keys that exist in the small table.

When I query against it, I use an INNER JOIN

SELECT * FROM my_bigtable AS big
  INNER JOIN my_smalltable AS small ON big.key = small.key;

Works great. But now I've added two columns (fname,lname) the small table. The big table has these columns, I want to pull the entries in these columns for reflected keys and put them in the columns of the small table.

INSERT INTO my_smalltable AS small
  SELECT FNAME,LNAME FROM my_bigtable AS big
    WHERE big.FNAME = small.FNAME
      AND big.LNAME = small.LNAME;

Is this going to only bring the records for the Unique Keys that exist in the small table to the small table or will it bring EVERYTHING from the big table into the small table, regardless if the unique key exists in the small table?

Upvotes: 0

Views: 147

Answers (2)

criticalfix
criticalfix

Reputation: 2870

Try:

UPDATE small
SET small.FNAME = big.FNAME,
small.LNAME = big.LNAME
FROM my_smalltable AS small
JOIN my_bigtable AS big
ON big.ID = small.ID

The (inner) join will only select records that exist in the small table.

But my_smalltable and my_bigtable really should not use the same ID field. They should each have their own primary key. You can use a foreign key between the two. For example:

FROM my_smalltable AS small
JOIN my_bigtable AS big
ON big.bigID = small.bigID

Where bigID is the primary key of my_bigtable, but a foreign key in my_smalltable (which has its own primary key as well my_smalltable.smallID.)

Upvotes: 1

rs.
rs.

Reputation: 27427

You need UPDATE statement not INSERT, try this:

update my_smalltable small
INNER JOIN my_bigtable AS big ON small.key = big.key
SET small.FNAME = big.FNAME,
small.LNAME = big.LNAME

Upvotes: 0

Related Questions