Menu
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!
830
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
INSERT INTO SELECT vs SELECT INTO: Both the statements could be used to copy data from one table to another. But INSERT INTO SELECT could be used only if the target table exists whereas SELECT INTO statement could be used even if the target table doesn't exist as it creates the target table if it doesn't exist.
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
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}'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
