Update Query Syntax Error

[dreid1011]

Good morning,

I am working on a seemingly very simple update query and I am receiving a Syntax Error. So far, my searches have not found similar issues, so at the moment, I am lost.

The query was originally an Append query, then I clicked on the Update button on the ribbon. This is the SQL it came with:

UPDATE ASSESS_V_MS_SPECIAL_ASSESSMENT SET 
WHERE (((ASSESS_V_MS_SPECIAL_ASSESSMENT.Year)=2018 Or (ASSESS_V_MS_SPECIAL_ASSESSMENT.Year)=2019));

I made a slight adjustment to match my table name:

UPDATE ASSESS_V_MS_SPECIAL_ASSESSMENTx1 SET 
WHERE (((ASSESS_V_MS_SPECIAL_ASSESSMENT.Year)=2018 Or (ASSESS_V_MS_SPECIAL_ASSESSMENT.Year)=2019));

When I receive the error, "WHERE" is highlighted. I've tried removing the () and adding [], but no luck.

Also, while I'm at it, a couple questions:

Is SET necessary as well? If it is, I'm not sure what it should be.

Can an Update query add/remove rows?

Thank you.

 

[xenou]

Hi,
you need to make your change in the table name everywhere it is used:

UPDATE ASSESS_V_MS_SPECIAL_ASSESSMENT[B][COLOR="#FF0000"]x1[/COLOR][/B] SET 
WHERE (((ASSESS_V_MS_SPECIAL_ASSESSMENT[B][COLOR="#FF0000"]x1[/COLOR][/B].Year)=2018 Or (ASSESS_V_MS_SPECIAL_ASSESSMENT[COLOR="#FF0000"][B]x1[/B][/COLOR].Year)=2019));

Note that as it stands you also need something to update:

UPDATE 
	ASSESS_V_MS_SPECIAL_ASSESSMENTx1 
[COLOR="#FF0000"]SET 
        ASSESS_V_MS_SPECIAL_ASSESSMENTx1.SOMEFIELD = SOMEVALUE[/COLOR]
WHERE 
	((
		(ASSESS_V_MS_SPECIAL_ASSESSMENTx1.Year)=2018 
		Or 
		(ASSESS_V_MS_SPECIAL_ASSESSMENTx1.Year)=2019
	));

In regard to your other questions, SET is necessary in update queries, and it will never add or remove rows, only update them.

 

[dreid1011]

Ok, I suppose I am confused then. If I make that change to all the table names to be the same, I need to use a FROM then to tell it which table to pull the updated data from?

Perhaps I don't need an update query at all then. None of my data values actually change in regards to being different from what they were. Throughout the year, rows will be added or removed from the master data source and, for the moment, my goal is to pull the most current two years from that source each month to reflect what has been added/removed. I imagine I probably want an Append query and a Delete query then.

 

[james_lankford]

> for the moment, my goal is to pull the most current two years from that source each month

then write a select query

do that first and make sure you're getting what you want before you start appending and deleting stuff

 

[dreid1011]

I already have many select queries. What I have is a link to a SQL database and one of the fields has the wrong type. It has short text for a field that should be number. I don't have the privileges to change the field type and those that do are taking their time and/or are busy. We run many select queries on this and other tables linking to the database. The problem is that we receive errors when trying to match that field among all the tables and that one is text while the others are number.

My work around was to make an append query to manually pull the data out of the table with the wrong field type into a blank local table where I can change the field type back to number. I also update some other local tables once a month from the database to run queries on. So, I was hoping I could make the query update this new local table with the added/removed rows from the source table without having to delete the table each month and recreate it so the append doesn't just keep adding to the data creating duplicates.

 

[james_lankford]

> to manually pull the data out of the table with the wrong field type
that's why you write a select query
to pull (and verify) the correct data you want

then you use the "select query" as the source for your append query

 

[xenou]

So, I was hoping I could make the query update this new local table with the added/removed rows from the source table without having to delete the table each month and recreate it so the append doesn't just keep adding to the data creating duplicates.

deleting and recreating the table once a month is not a bad option, by the way.

Remember to compact and repair after you do so, though, if using this strategy, to free up space in the database, as MSAccess will not do so automatically.

 

[dreid1011]

deleting and recreating the table once a month is not a bad option, by the way.

Remember to compact and repair after you do so, though, if using this strategy, to free up space in the database, as MSAccess will not do so automatically.

No, I didn't think so, I just like to see if I can make things as simple as possible as I may not be the only one needing to do it in the future. But hopefully the powers that be will fix the source so I don't have to work around. And thank you for the reminder.

 

[dreid1011]

> to manually pull the data out of the table with the wrong field type
that's why you write a select query
to pull (and verify) the correct data you want

then you use the "select query" as the source for your append query

If the select query is pulling it from the source as a short text value, without using casting, would the append query then be able to add it to the local table where it is a number value instead, without issue of course?

 

[xenou]

You can just try it and see if it works. Should be a straightforward test to see if you can append a short text value into a number field in a table. If it doesn't work, you simply do the casting you need to do in the append query.

Note that for what it's worth, this did work for me:

INSERT INTO Table9 ( F2 )
SELECT Table8.F1
FROM Table8;

where Table8 was the values "1","2","3","4" and the field F2 in Table9 was Number (Integer).

This shows that the conversion from text to number can occur automatically. You should still test thoroughly in your actual situation with more robust tests. For one, you want to be sure that all the text values really are numbers, or you will encounter errors. Also they should be compatible datatypes (if you try to store 1.2, which is a decimal value with fractions, in an integer field, which will not hold fractional values, then you will at best lose information and at worst lose the record). Also since from your descriptions it sounds like you have data from an outside source, my test will not be exactly under the same conditions as yours (MSAccess only, vs. MSAcces interacting with another database).