CODEWITHSUNDEEP
mysqlselectinsert-into
Josh
Josh

Reputation: 1421

Inserting database row with values from another table

Basically, I have two tables: images and servers. When I want to insert a row into the images table, I need to specify a s_id as one of the fields. Problem is, I only have name, which is another field in the servers table. I need to find what s_id belongs to name, and then use that in my INSERT INTO query on the images table.

Maybe this image will help: https://i.sstatic.net/DyMRJ.png

I only know the name field from the servers table, and I need to use it to get the s_id field from the servers table. When I have that, I can use it in my INSERT INTO query, as it's a foreign key.

I found this: http://www.1keydata.com/sql/sqlinsert.html

But it just confused me even more.

One solution would be to run two queries. One to get the s_id, and one to run the insert query. But I'd like to limit the amount of queries I run if there's a reasonable alternative.

Thanks!

Upvotes: 0

Views: 1911

Answers (3)

Brian Driscoll
Brian Driscoll

Reputation: 19635

Please see my comment above regarding a potential data integrity issue. I am assuming that the name field in your server table has a unique constraint placed on it.

There are a couple of ways that you can approach this INSERT, and I'm sure that some are better than others. I make no claim that my way is the best way, but it should work. I don't know how you're writing this query, so I'm going to use @FieldValue to represent the variable input. My approach is to use a subquery in your insert statement to get the data that you require.

INSERT INTO images (field1, field2... s_id) VALUES ('@field1val', '@field2val'... (SELECT s_id FROM servers WHERE name='@nameval'));

Upvotes: 0

Rex Morgan
Rex Morgan

Reputation: 3029

You should be able to do something like this, but you'll need to fill in the items in <> with the values you want to insert.

INSERT INTO images (s_id, u_id, name, filename, uploaded)
   (SELECT s_id, <u_id>, <name>, <filename>, <uploaded>
   FROM imgstore.servers
   WHERE name = @server_name)

This is the syntax for SQL Server, but I think it will work with MySQL as well.

Here's an article on INSERT ... SELECT Syntax

Upvotes: 0

mu is too short
mu is too short

Reputation: 434635

You can use the INSERT ... SELECT form, something like this (with real column names and values of course):

INSERT INTO images (s_id, u_id, name, filename, uploaded)
SELECT s_id, ...
FROM servers
WHERE name = 'the name'

I don't know where you're getting the u_id, name, filename, or uploaded column values for images but you can include them as literal values in the SELECT:

INSERT INTO images (s_id, u_id, name, filename, uploaded)
SELECT s_id, 11, 'pancakes', 'pancakes.jpg', '2011-05-28 11:23:42'
FROM servers
WHERE name = 'the name'

This sort of thing will insert multiple values if servers.name is not unique.

Upvotes: 3

Related Questions