Insert Subqueries

[pltaylor3]

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any help

[Stu]

The syntax you are looking for is:

INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'

However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?

HTH,
Stu
pl*******@gmail.com wrote:

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any help

[pltaylor3]

thanks for your reply...the names are legacy. Just trying to make it
more functional.
Stu wrote:

The syntax you are looking for is:

INSERT INTO tblPartLocation (PartLocation, Part)
SELECT 999, PartID
FROM tblParts
WHERE PartName = 'test'

However, you may also want to reexamine your schema. Don't use tbl as
a prefix for your tables; it's redundant, and unnecessary. Also, you
have a column named Part in one table, but you're inserting the values
of PartID from another table. If the columns represent the same thing,
why don't you name them the same?

HTH,
Stu
pl*******@gmail.com wrote:

I have tried this insert comand and it errors out telling me that i
cannot use subqueries this way. INSERT INTO tblPartLocation
(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHERE
PartName = 'test'))

how would i insert a value from a query?

thanks for any help