SQL nested SELECT inside INSERT

Question

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'
)
)

Gordon Linoff · Accepted Answer · 2016-09-15 11:36:34Z

1

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.

answered Sep 15, 2016 at 11:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

rtom Over a year ago

It works, thanks. The select statement confused me a bit because i didn't know i can put values that are not even in table there.

Collectives™ on Stack Overflow

SQL nested SELECT inside INSERT

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