MYSQL INSERT getting data from INNER JOIN

Question

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?

criticalfix · Accepted Answer · 2013-03-13 18:09:23Z

1

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.)

answered Mar 13, 2013 at 18:09

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rs. Over a year ago

I think this update statement syntax is valid in SQL Server not in MySQL

rs. · Accepted Answer · 2013-03-13 18:08:24Z

0

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

answered Mar 13, 2013 at 18:08

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Collectives™ on Stack Overflow

MYSQL INSERT getting data from INNER JOIN

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related