[ms office - 2010] Access Query - Update SQL statement Help please

[RabbieBurns]

Im trying to write an update query to update multiple records, but the records i want to update criteria is different for each.

 

eg:

UPDATE ASSET SET Location = 'Senior'
WHERE (((ASSET.[serialNumber])='1234567890'));

 

The above works great but I have a bunch of serial numbers I want to do at once and have been updating the query each time with the next serial but surely theres an easier way?

[SYNACK]

I think you can do it via chaining or the OR modifer in full SQL ie

UPDATE ASSET SET Location = 'Senior'

WHERE (((ASSET.[serialNumber])='1234567890') OR (ASSET.[serialNumber])='1234567890'));

[code]
 

You may also be able to try more advanced methods like the ones from here: SQL: UPDATE Statement
 

It depends on what version of SQL you are using though, MSSQL is much more powerful than MySQL which is vastly more powerful than Access which takes some single line SQL syntax but not all.

[pcstru]

For one off updates (where there is no point in writing a reusable program), I paste the list (of say serial numbers) into excel, then use the concatenate() function to generate the individual SQL statements. Then copy the text from the resulting column into the SQL client. It takes longer to describe it than it does to do! Edited July 26, 2012 by pcstru