Gerhardpet
asked on
Run SQL statement in Microsoft Access
Is there a way to run the following SQL statement in Microsoft Access. I have over 1000 row needed to update like this and I can't find a way to do in Access just like I would in MS SQL Management Studio or any other database.
update BOM_SUB_LEVEL SET [TOP_CODE] = 'R1813' WHERE [TOP_CODE] = 'F-R1813';
update BOM_SUB_LEVEL SET [TOP_CODE] = 'R1816' WHERE [TOP_CODE] = 'F-R1816';
update BOM_SUB_LEVEL SET [TOP_CODE] = 'R1813' WHERE [TOP_CODE] = 'F-R1813';
update BOM_SUB_LEVEL SET [TOP_CODE] = 'R1816' WHERE [TOP_CODE] = 'F-R1816';
ASKER
No that is not the case. The sample I gave above it is just the F- removed but it can be all over the board some of them being completely different in part number
I see.
Access unfortunately does not behave like SSMS, so you would have to handle each distinct sort of "fix" separately. The example I give shows you how to strip off the first 3 characters. To remove the first 3, you would change LEN(TOP-CODE)-2) to LEN(TOP-CODE)-3), for example
Access unfortunately does not behave like SSMS, so you would have to handle each distinct sort of "fix" separately. The example I give shows you how to strip off the first 3 characters. To remove the first 3, you would change LEN(TOP-CODE)-2) to LEN(TOP-CODE)-3), for example
ASKER
I know how to strip the first 3 characters. That is not the issue here. I guess it is not posible then
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have the old and new values in an excel spreadsheet. Had not thought of your suggesting but yes that will work fine. Thank you for your help!
You may have to import the worksheet and add a primary key. Update queries don't normally work if linked Excel tables are included since the Excel tables are not updateable using SQL.
ASKER
Thanks for the extra info
You're welcome. Just trying to head off the next question:)
UPDATE BOM_SUB_LEVEL SET [TOP_CODE]=RIGHT(TOP_CODE,
Essentially this strips off the first two characters of TOP_CODE. Of course, you should test this on a TEST database before you apply it to your LIVE database