CODEWITHSUNDEEP
sqlms-accesssyntaxwhere-clause
user3930153
user3930153

Reputation: 33

where statement in Access 2007-2010 sql

I am trying to pull data from one table into a database set up, by getting it to match the product codes then pull the data form the table field into the database field.

I have the below statement, but when I try to run it I get an error saying "Syntax error in Update statement" and it then highlights the where part which I'm guessing has a mistake in it, as I am new to this I don't know what is is.

UPDATE [Raw material reg info].[SAP Material Number] 
SET [Sheet1].[SAP Material Number] 
WHERE [Raw material reg info].[CMC Part Code] LIKE [Sheet1].[CMC Part Code];

Upvotes: 0

Views: 134

Answers (2)

Jamie Dunstan
Jamie Dunstan

Reputation: 3765

Based upon your query and your description, I think this is what you really want. I may be completely wrong, but I can only assume that you want to update the value of the [SAP Material Number] field in the [Raw material reg info] table where you find a matching [CMC Part Code] in the imported [Sheet1] data.

UPDATE 
    [Raw material reg info]
SET
    [SAP Material Number] = [Sheet1].[SAP Material Number]
FROM
    [Sheet1]
INNER JOIN
    [Raw material reg info] ON [Sheet1].[CMC Part Code] = [Raw material reg info].[CMC Part Code]

Edit:

According to this post, the syntax for Access is slightly different for the above query. Try this:

UPDATE 
    [Raw material reg info]
INNER JOIN
    [Sheet1] ON [Raw material reg info].[CMC Part Code] = [Sheet1].[CMC Part Code]
SET
    [Raw material reg info].[SAP Material Number] = [Sheet1].[SAP Material Number]

Upvotes: 0

SEB BINFIELD
SEB BINFIELD

Reputation: 410

UPDATE [Raw material reg info].[SAP Material Number] 
SET [Sheet1].[SAP Material Number] = *Need to specifiy what you want this value to be *
WHERE [Raw material reg info].[CMC Part Code] LIKE [Sheet1].[CMC Part Code];

Upvotes: 1

Related Questions