rtom rtom - 2 months ago 18
MySQL Question

SQL nested SELECT inside INSERT

I want to insert value that i get with SELECT to table, but i have no idea how to format it so it's work. When i try the SELECT statement alone, it returns value that i want, but when its nested it returns 0 which it shouldn't. I tried wrapping it in

"" ''
but it doesn't work.

INSERT
INTO
brand(brand_id,
NAME,
cat_id)
VALUES(
NULL,
"value",
(
SELECT
cat_id
FROM
category
WHERE
cat_id = 'number'
)
)

Answer

Just use insert . . . select. Leave out the values:

INSERT INTO brand(brand_id, NAME, cat_id)
  SELECT NULL, 'value', cat_id
  FROM category
  WHERE cat_id = 'number';

Three comments:

  • If number is really a number, then don't use quotes.
  • This is slightly different from your version because it will insert no rows if there are no matches.
  • If the subquery returns exactly one row, then your version should work.