Insert Into ... Select Syntax

kctaylor

I am building a performance tracking repository. I have a table of requirements and a table of personnel.

A user selects the person from the personnel list that should have a requirements checklist; the checklist selected is driven by the personnel rank. The personnel table is masterYM, the generic requirements checklist is in refDTGReqtHdr, and the target is the dTGPerfHdr.

To pull this off, I am attempting to use a "Insert Into...Select" query. I have coded what I thought was clean syntax, but get a warning that I have an error in my SQL sntax, specifically, "You have an error in your SQL syntax near 'WHERE masterYM.PrsthdID = refDTGReqtHdr.PrsthdID AND master YM.YMID =' at line 3.

Can anyone identify an issue with the syntax?

mysql_query("INSERT INTO dTGPerfHdr (YMID, EntityID, PrsthdID, SectionID, Required)
SELECT masterYM.YMID, masterYM.EntityID, masterYM.PrsthdID, refDTGReqtHdr.SectionID, refDTGReqtHdr.Required
WHERE masterYM.PrsthdID = refDTGReqtHdr.PrsthdID
AND masterYM.YMID = $HTTP_POST_VARS['YMID']")
or die (mysql_error());

Thanks in advance for the help.

K0ma

The syntax for INSERT is:

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

The syntax for AND is:

SELECT column FROM table
WHERE column operator value
AND column operator value

The first problem I see is you are making an INSERT INTO with no
VALUES... This mean your insering nothing into your database.

The problem I see with your AND statement is that you don't have a FROM clause. You are SELECTing a table from an
unnamed source.

Fix those two problems and you should be OK.

kctaylor

MySQL supports two forms of INSERT, one is the INSERT...VALUES clause (which you refer to) and the other is the INSERT...SELECT clause (which I am attempting to debug).

From the MySQL manual, section 6.4.3.1:

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...
With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

Note that the clause does not make use of a VALUES statement. As to the WHERE, I don't understand your reference to a FROM in the AND statement, since the values are fully specified as to table and column.

Have you made use of the INSERT...SELECT previously? If so, do you have a sample of working code?

nnichols

You are missing the FROM clause in the SELECT section of your INSERT ... SELECT

$sql = "INSERT INTO dTGPerfHdr (YMID, EntityID, PrsthdID, SectionID, Required) 
SELECT masterYM.YMID, masterYM.EntityID, masterYM.PrsthdID, refDTGReqtHdr.SectionID, refDTGReqtHdr.Required 
FROM masterYM, refDTGReqtHdr
WHERE masterYM.PrsthdID = refDTGReqtHdr.PrsthdID 
AND masterYM.YMID = $HTTP_POST_VARS['YMID']";

$res = mysql_query($sql) or die ('Error: ' . mysql_error() . '<br>Query: ' . $sql);

kctaylor

Thanks! I worked on this so long, seems I missed the most obvious. Working fine now. Appreciate your help!