MySQL JOIN when inserting data

Question

I have two tables: images and servers. Here's the schema relating to those two tables:

id, name, uploaded, views, server_id

id, name, description, drive_space, enabled

On the images table, server_id is a foreign key to the id field in the servers table. Pretty simple stuff. Many images belong to one server.

When I insert an image, it has to have a key pointing to the server the image is hosted on. When I insert the row, I'm given the name of the server (not its id) so I can't just insert it. I can achieve what I want with two queries (one to get the server ID and another to insert the image into the database) but idealy I want it done in one query with a JOIN as it's best practice.

I'm almost clueless with JOINS when it comes to SELECT statements and even more so with an INSERT. Can anyone help me out?

Just Click HERE It will help you to Understand the Foreign Key Concept... — Rush
– Rush, Commented Jul 11, 2012 at 20:21

Mark Byers · Accepted Answer · 2012-07-11 20:15:14Z

2

No need for a join. Just use a INSERT ... SELECT ...:

INSERT INTO images
(name, uploaded, views, server_id)
SELECT 'imagename', CURTIME(), 0, id
FROM servers
WHERE name = 'servername'

answered Jul 11, 2012 at 20:15

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

spencer7593 Over a year ago

+1. This is the way to go. But, if we don't have a guarantee of uniqueness of the servers.name column, we can add a LIMIT 1 to avoid the insert adding more than 1 row. Also, there's a possibility that the servername value will not be found, so the app would need to check that rows_affected > 0, and handle the condition when it isn't, rather than assuming a row was successfully inserted.

Collectives™ on Stack Overflow

MySQL JOIN when inserting data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related