[SOLVED] Nesting a SELECT in an INSERT

[riT-k0MA]

Hi

I haven't found any definite information anywhere I've looked as to whether or not one can throw a SELECT into an INSERT Statement:

 

INSERT INTO Table1(Col1, Col2, Col3)
VALUES(Value1, SELECT Col6 FROM Table2 WHERE Col7 = 'aVariable' LIMIT 1, value3)

 

What I'm trying to do is select a value from one column where it corresponds to some text that was retrieved a page earlier. (as in DeptNO and DeptDescription being the 2 columns)

 

Is this possible?

 

If it is, It would save a huge amount of code.

 

Thanks in Advance

riT-k0MA

[jackpf]

What are you trying to do?

 

This isn't really how queries are structured.

[riT-k0MA]

What are you trying to do?

 

This isn't really how queries are structured.

 

What do you mean?

 

If I sub in the TableNames and cols for values (Think of them as variables), I get a working query, (excluding the SELECT subquery)

[revraz]

Did you try it to see what the result/error was?

 

INSERT INTO Table1(Col1, Col2, Col3)

VALUES(Value1, (SELECT Col6 FROM Table2 WHERE Col7 = 'aVariable' LIMIT 1), value3)

 

If this is in code like PHP, another option would be to just get the variable before doing the Insert statement.

[riT-k0MA]

After some fiddling around I realised it was my syntax that caused the problem.

It is possible to nest a SELECT in an INSERT So long as the table boing updated does not appear in the SELECT.

Here's the example I made:

 

CREATE TABLE Animal
(
  AnimalID INT,
  AnimalName VarChar(20)
)

CREATE TABLE Owner
(
  OwnerID INT,
  OwnerName VarChar(20),
  Animal INT,
  FOREIGN KEY (Animal) REFERENCES  Animal(AnimalID)
)

INSERT INTO Animal (AnimalID, AnimalName)
VALUES(1, 'Fred')

INSERT INTO Owner (OwnerID, OwnerName, Animal)
VALUES(12, 'Bob', (SELECT AnimalID FROM Animal WHERE AnimalName='Fred'))

 

Thanks for all the help.

 

If this is in code like PHP, another option would be to just get the variable before doing the Insert statement.

 

I was trying to avoid that, But I have one Self-referencing variable, so I'll have to do that. As for the rest, My job is a lot easier now