SQL Insert Statement

[Garrett!]

I'm looking to insert some data into my table called tblModelStandards. tblModelStandards has the fields ModelStandardsID, PlantCode, Category, SubCategory, Description, Cost. I'm trying to insert "Door Safety Chain" and "$3.00" into Description and Cost where Category is "Opening" and SubCategory is "Side Door". I just don't know how to insert the ModelNumber for each record since it changes. The new records with Door Safety Chain will have a category of "Opening and SubCategory of "Side Door" too. I think the SQL statement would look something like this:

INSERT INTO tblModelStandards ( PlantCode, ModelNum, Category, SubCategory, Description, Cost)
VALUES ('GA', ????? , 'Opening', 'Side Door', 'Safety Door Chain', '$3.00')
WHERE Category = 'Opening' AND 'SubCategory = 'Side Door';

I copied my data into an Excel spreadsheet, made the change, and copied it back. I would really like to learn how to do this with a SQL command.

Thanks!

 

[plog]

You really need to use the right terms. Usually I can see through peoples words to grasp what they are actually trying to do, but yours is still not clear.

INSERT (aka append query) - Adds new rows of data to a table. This doesn't involve a WHERE clause or any criteria.

UPDATE - changes data in fields of existing rows. This can involve a WHERE clause.

Which is it that you are trying to do? UPDATE existing records or INSERT new records?

 

[Garrett!]

I am trying to Insert/Append the table.

 

[plog]

Then your INSERT statement wouldn't include a WHERE clause.

 

[Garrett!]

Is there a way to run the SQL statement to only insert records where the category and subcategory equal "Opening" and "Side Door"? For example, if my record was: (111, GA, Trailer1, Opening, Side Door, Red Door, $400) I would want to insert (112, GA, Trailer1, Opening, Side Door, Safety Door Chain, $3). If Trailer2 did not have "Opening" and "Side Door", I would not insert a record. I guess that's why I thought I needed a WHERE clause because I don't want to insert Safety Chain Door onto every associated trailer.

 

[plog]

So you want to insert data from TableA into TableB, but only for records in TableA that meet certain criteria? Here's how--

Create a query based on TableA, bring down all the fields that you want to be moved into TableB. Apply the criteria you desire, then run that query. Right now, its a SELECT queryr so it will display the results, make sure they look correct. When they do, go back into design view and change the query to an APPEND query by clicking the appropriate button in the ribbon. It will ask what table to APPEND them to, set it to TableB. The fields in the bottom of the query will get a new row which tells you which fields in TableB the data in TableA will go into. Make sure they are correct and when they all are, run that query and the data will be appended to TableB.