MySQL Insert with select subquery for one value

Question

I have a MySQL insert query which needs to pull one data field from another table and I was wondering if this can be done with a select subquery

INSERT INTO resources
(
     client_account_id,
     date_time,
     resource_name,
     resource_description_id,
)
VALUES
(
      {0},
      '{1}',
      '{2}',
      {3},
 )

and I need a select query to get the resource_description_id from another table

SELECT resource_description_id 
FROM resource_descriptions 
WHERE resource_description = '{0}'

I have seen examples for duplicating entire tables, but I'm not sure how this can be done when just one field is needed from another table and the other fields come from a form.

Thanks!

Jim · Accepted Answer · 2012-03-12 17:37:52Z

9

Your subquery can just SELECT the values it needs. Something like:

INSERT INTO resources
(
     client_account_id,
     date_time,
     resource_name,
     resource_description_id,
)
SELECT {0}, '{1}','{2}', resource_description_id 
FROM resource_descriptions 
WHERE resource_description = '{3}'

answered Mar 12, 2012 at 17:37

Jim

22.7k6 gold badges55 silver badges81 bronze badges

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

2 Comments

kasavbere Over a year ago

The problem with this answer is if resource_description is not a match, then you get nothing. Have you tried running your reply?

mike Over a year ago

Well I have a form which a user submits resource name, description, and keywords, Before I insert into the resource table I insert into the resource description table, so the description will always match. But that makes me think there is the possibility of duplicate resource descriptions. Is there a way to pull the most recently inserted row from a table?

kasavbere · Accepted Answer · 2012-03-12 17:43:03Z

2

INSERT INTO resources
(
 client_account_id,
 date_time,
 resource_name,
 resource_description_id,
)
VALUES
(
  {0},
  '{1}',
  '{2}',
  SELECT resource_description_id 
FROM resource_descriptions 
WHERE resource_description = '{0}'
 )

answered Mar 12, 2012 at 17:43

kasavbere

6,02314 gold badges52 silver badges73 bronze badges

Comments

Teja · Accepted Answer · 2012-03-12 17:37:34Z

1

INSERT INTO resources
(
     resource_description_id
)
(
SELECT resource_description_id 
FROM resource_descriptions 
WHERE resource_description = '{0}'
)

answered Mar 12, 2012 at 17:37

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Comments

Chetter Hummin · Accepted Answer · 2012-03-12 17:42:19Z

0

You can do an insert using the results of a select query

INSERT INTO resources
(
 client_account_id,
 date_time,
 resource_name,
 resource_description_id
)
SELECT 
  '{0}',
  '{1}',
  '{2}',
  resource_description_id
FROM resource_descriptions 
WHERE resource_description = '{0}'

answered Mar 12, 2012 at 17:42

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Comments

EternalHour · Accepted Answer · 2017-06-05 04:43:27Z

0

MySQL didn't like most of these queries unless I added parenthesis around the subquery. Here is what I used.

INSERT INTO resources
(
 client_account_id,
 date_time,
 resource_name,
 resource_description_id,
)
VALUES
(
  '{0}',
  '{1}',
  '{2}',
  (SELECT resource_description_id 
   FROM resource_descriptions 
   WHERE resource_description = '{0}')
)

answered Jun 5, 2017 at 4:43

EternalHour

8,6776 gold badges40 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL Insert with select subquery for one value

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related