CODEWITHSUNDEEP
sqlsql-serverdatabase
Taher
Taher

Reputation: 593

Insert into a table values that are in another table matching condition

I have 2 tables (AllProducts) and (Categories), some product can have more than one category so I have used a 3rd table (Product_Category) to deal with this many-to-many relation.

During my first design of the database I created a table for each category and populated them with the products name (product name is unique).

Now, I am trying to insert the data that are in the categories tables I created earlier into the new table (Product_Category) so I wrote a query like this:

INSERT INTO Product_Category(prodID,categoryID)
  SELECT
    (SELECT ID FROM AllProducts 
     WHERE ProductName IN(SELECT Products FROM Poltries))
   ,(SELECT 1)

But I am getting this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I understand why I am getting the error but I don't know how to write it.

What I want is:

Select the ID of every product from (AllProducts) table that its name is in Category_A table (in this example 'Polteries') then insert this value into the column [prodID] and then insert a constant value (category ID) into the column [categoryID].

Can anyone help me with this?

Upvotes: 0

Views: 1852

Answers (1)

Raj More
Raj More

Reputation: 48018

Try this

INSERT INTO Product_Category(prodID,categoryID)
SELECT ID, 1
FROM AllProducts 
WHERE ProductName IN (SELECT Products FROM Poltries)

Upvotes: 2

Related Questions