SQL Syntax Help for INSERT INTO WHERE...

[Gladimir]

Here is my current SQL statement that produces a syntax error:

 

"INSERT INTO " + strTableName + " (DescID) " +
  "SELECT DescID FROM " + strTableName + " WHERE (Description='" + strDescription + "') " +
  "VALUES(" + intDescID.ToString() + ")";

 

I came across one of those SQL Tutorial sites that said something like this would be legal, but I had my doubts from the beginning.

 

What I am trying to do is INSERT INTO myTable (DescID) VALUES(intDescID) WHERE (Description='strDescription')". I just don't know the proper syntax or even if it is possible to do with a single SQL statement.

 

Background

 

I select distinct descriptions and come up with about 4000 rows. For each of those distinct descriptions, I am trying to use my INSERT statement to update records in another table. Sometimes 'Description' equals strDescription in only one row, and other times that criteria will return just under 10,000 rows or matches.

[kleptos]

You can do the following in SQL just fine. But you have to have the corrct number of columns that the table has.

INSERT INTO table
SELECT col1, col2, col3, col4
FROM other_table
WHERE col1 = condition

[Nerseus]

You're combining the two basic forms of the INSERT. You either want the SELECT or the VALUES, but not both. For instance:

INSERT INTO Table1 (DescID)
SELECT DescID FROM Table2 WHERE MyColumn = 'MyValue'

OR

INSERT INTO Table1 (DescID)
VALUES (123)

 

When using VALUES you must provide an actual value, not a select. If you have a combination of known hard-coded values and values from a table, you use the SELECT as in:

INSERT INTO Table1 (DescID, Active, UserName)
SELECT DescID, 1, 'djones' FROM Table2 WHERE MyColumn = 'MyValue'

In the above, the "1" and "'djones'" are hard-coded - they have nothing to do with Table2.

 

-Nerseus

[Gladimir]

Patience for a slow one...

 

Ok, so these responses would indicate to me that I need two queries; one to select the records matching the description and a second to insert that description's id into the DescID column. Please correct me if I am wrong.

 

Here is a sample of information from table REF_CSDSoftware:

recID	Description	            DescID
339480	expinst		                0
339481	Internet Explorer ReadMe	0

It's the zeros in DescID that the INSERT will correct.

 

Here is a sample from table REF_MasterDescription:

DescID	Description	       CategoryCode
1	Adobe Acrobat Writer	1
2	Adobe PageMaker 5.0	1

 

In a separate function, I select the distinct descriptions from REF_CSDSoftware, assign each one a unique DescID, and insert that into the REF_MasterDescription table.

 

I then take that same unique DescID and the corresponding description and pass it to a function that queries all records from REF_CSDSoftware matching the description. I would like to simply insert that DescID into the results of that query, which is the one I'm trying to build.

 

Here is some code:

int intDescID = 0;
string strDescription = null;
// drc is distinct descriptions from table REF_CSDSoftware
foreach (DataRow row in drc)
{
  strDescription = row["Description"].ToString();
  // does description already exist in REF_MasterDescription
  intDescID = LookupDescID(strDescription);
  if (intDescID < 1)	// id does not exist in master list
  {
     // insert new Description and DescID in the master list
     InsertNewDescription(strDescription, eCode);
     intDescID = LookupDescID(strDescription);
  }
  // Get all records matching description from REF_CSDSoftware
  // and assign the new DescID to all rows with that description
  InsertMasterDescriptionID(intDescID, strDescription, eCode);
  Application.DoEvents();
}

 

So, am I correct in assuming InsertMasterDescriptionID will first have to SELECT all rows matching strDescription, the follow that with a separate query to INSERT the corresponding intDescID?

[Gladimir]

SOLUTION

 

Nevermind... I think the UPDATE command will do the trick.