Sql update query

[ct2013]

I have a db called "OUTY"

in it a table called "Students"

in the table a field called "class"



I want to make an update query to update
where class = '5' update to '6'
where class = '4' update to '5'
where class = '3' update to '4'
.......

Ive tried all codes posted and cant get around them.

Please help.

Ta

 

[EternalMyrtle]

This is a straightforward update query, no code required.

Just go to make a query in design view. Add the tables you need to the query (so, "Students"). Then go to "Design Type" tab and under "Query Type", choose "Update".

Put the fields you want (so, "class") and enter the new value in the "Update to" box and the current value in the "Criteria" field. I would do one update per query rather than trying to write an expression to do them all at once in case something goes wrong.

You can view in datasheet to see how many fields will be updated.

Then select "Run" (red exclamation point). Note that it will update without any notice so you should save a backup first just to be sure.

Hope this helps.

 

[ct2013]

Thanks a lot, Just a lot of updates i need and don't feel like clogging my DB with loads of Queries if poss.

Ta

 

[EternalMyrtle]

By the way, you may be able to add the same field several times in the same update query to do them all at once. I have never tried this. If you backup the database beforehand and have a lot of fields to update, it would be worth trying.

 

[EternalMyrtle]

You don't need to save the queries... if you don't save them, then the database will not be "clogged".

 

[ct2013]

Doesnt let me do multiple updates in 1 query! Want to make a run button (Save) so that the user can run the update and not drive me mad every time!!!

ta

 

[EternalMyrtle]

Ok, do this

IIf([class]=5,6) & IIf([class]=6,7)& IIf([class]=7,8) and so on

 

[EternalMyrtle]

In the update to. Sorry.

In the criteria put 5 Or 6 Or 7, etc.

 

[EternalMyrtle]

This is the SQL:

UPDATE students SET students.class = IIf([class]=3,4) & IIf([class]=4,5) & IIf([class]=5,6)
WHERE (((students.class)=3 Or (students.class)=4 Or (students.class)=5));

 

[ct2013]

Many thanks for your help, will try it later.

Ta

 

[ct2013]

Sorry to bother, not sure what im doing wrong, tried that script in a query (SQL view) got error "invalid sql statement, expected delete, insert, update..". tried a few changes and not getting anywhere.

Thanks

 

[EternalMyrtle]

See the attached example. I tested it and it works.

Look at the students table and close it (all 3s, 4s and 5s). Then open the query in design view to see the settings and hit the red exclamation mark to run it. It will tell you that 9 rows will be updated. You will see that the table is now updated with the new values.

You can then check the SQL view and copy that entering your table and field names and values.

If something is misspelled or the syntax is wrong, it won't work.

Also, Is the field's data type really a number?

 

[EternalMyrtle]

If the data type for the class field is text, not number, you need to put the values in quotes. So, the SQL will look like this:

UPDATE Students SET Students.Class = IIf([class]="3","4") & IIf([class]="4","5") & IIf([class]="5","6")
WHERE (((Students.Class)="3" Or (Students.Class)="4" Or (Students.Class)="5"));

Again, the table, field names and values have to be the exact ones you are using.

 

[spikepl]

UPDATE Students SET Students.Class = Students.Class + 1 WHERE Students.Class IN (3,4,5)

or

if those things are strings (why are they?)

UPDATE Students SET Students.Class = Str(Cint(Students.Class) + 1) WHERE Students.Class IN ('3','4','5')

 

[ct2013]

Thanks very much for all your help, got it going now.

Ta

 

[EternalMyrtle]

Thanks for posting your much cleaner solution, Spikepl. Will keep it in mind.

 

[ct2013]

Sorry to drive you mad, i am new to this site and cant remember how i posted this thread but want to post another. Please advise how to.

Thanks

 

[EternalMyrtle]

Hi, I am assuming you figured this out already, but just in case you haven't...

Go into the main page of the forum in which you want to post and look for the button that says "New Thread".

See screenshot.

 

[ct2013]

Hi there,

I unfortunately don't have that button on my screen, maybe you can help.

Ta

 

[EternalMyrtle]

The button will only be available on the main page of the forum (so not on this page since you are in a thread).

So, to get to the main Queries forum from this page go up to the navigation "breadcrumbs" and select queries (see Picture, red circle). When you are in there, you will see the New Thread button as pictured in my previous post.

Or simply select the "Forums" tab and choose the forum category you want (also circled in red).