SQL insert subquery error

Question

I have an insert query with two subqueries:

INSERT INTO Work_Order (ID ,BRANCHID,BRANDID)   
VALUES (66),
SELECT ID FROM Brands WHERE NAME = 'branch'
SELECT ID FROM Branches WHERE NAME = 'brand'

I know it's not a correct syntax but I need the correct one, the ID must be 66 and BRANCHID,BRANDID are foreign keys for another tables

if you are inserting something into a database by fetching that something from another table, then your design is wrong. I say rethink. — nawfal
– nawfal, Commented Jul 22, 2012 at 19:40
look I am not a DB professional , the design consists of 3 tables the first is the one that I am inserting to, BRANCHID is a foreign key for another table called branches and the same for brands , the user writes the name of the branch and I will insert its ID , I think it's a good design, isn't it?? — Ahmed Kato
– Ahmed Kato, Commented Jul 22, 2012 at 21:55
oh stupid of me, thats fine, I for a moment thought you were inserting 'branch', 'brand' etc and not their ids. Apologies.. :) — nawfal
– nawfal, Commented Jul 23, 2012 at 6:36

Michael Berkowski · Accepted Answer · 2012-07-22 12:15:36Z

4

66 is a literal, use it and two subselects in your SELECT:

INSERT INTO Work_Order (ID ,BRANCHID,BRANDID) 
SELECT
  66 AS ID,
  (SELECT ID FROM Brands WHERE NAME='branch') AS BRANCHID,
  (SELECT ID FROM Brands WHERE NAME='brand') AS BRANDID

MySQL is lenient about the existence of a FROM clause, so this ought to work. Many other RDBMS would require you to put in a FROM clause with some table even though it isn't used in the SELECT (like Oracle's Dual table).

answered Jul 22, 2012 at 12:15

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Comments

Ahmed Kato · Accepted Answer · 2012-07-22 19:33:34Z

1

This is how it worked perfectly with me

INSERT INTO Work_Order (NUMBER,BRANDID,BRANCHID)    
SELECT 66,B.ID,Br.ID
FROM Brands as B,Branches as Br
WHERE B.NAME = 'brand' AND Br.NAME = 'branch'

answered Jul 22, 2012 at 19:33

Ahmed Kato

1,7076 gold badges29 silver badges54 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2012-07-22 13:28:30Z

0

If your subqueries return more than one row, you have to decide how to combine the values. The following query assumes you want all combinations:

INSERT INTO Work_Order (ID ,BRANCHID, BRANDID)
    select 66, branches.id, brands.id
    from brands cross join branches
    where branches.NAME = 'branch' and
          brands.NAME = 'brand'

(I changed the where clause to look for the name within the same table as the name . . . branches.Name = 'branch' rather than branches.name = 'brand'.)

answered Jul 22, 2012 at 13:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL insert subquery error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related