Update multiple ID#s 1

[cumap]

Hi all,

I have a need to link my database to another database's USERS table where ID#s are totally different from the current one. The goal now is that I will need to update a couple of tables which linked to the USERS.USERID with the new IDs from the new USERS table.

Here is what I got:

UPDATE RMA " _
& " SET UserID = CASE UserID " _
& " 	WHEN 1 THEN 9 " _
& " 	WHEN 6 THEN 12 " _
& " 	WHEN 7 THEN 19 " _
& " 	WHEN 8 THEN 16 " _
& " 	WHEN 9 THEN 14 " _
& " 	WHEN 10 THEN 17 " _
& " 	WHEN 11 THEN 11 " _
& " 	WHEN 12 THEN 15 " _
& " 	WHEN 13 THEN 18 " _
& " 	WHEN 14 THEN 21 " _
& " 	WHEN 15 THEN 22 " _
& " 	WHEN 16 THEN 23 " _
& " 	WHEN 17 THEN 24 " _
& " 	WHEN 18 THEN 25 " _
& " 	WHEN 19 THEN 16 " _
& " 	WHEN 20 THEN 20 " _
& " END " _
& " WHERE UserID IN (1,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)

Here is an error while running the query:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'CASE UserID WHEN 1 THEN 9 WHEN 6 THEN 12 WHEN 7 THEN 19 WHEN 8 THEN 16 WHEN 9 THEN 14 WHEN 10 THEN 17 WHEN 11 THEN 11 WHEN 12 THEN 15 WHEN 13 THEN 18 WHEN 14 THEN 21 WHEN 15 THEN 22 WHEN 16 THEN 23 WHEN 17 THEN 24 WHEN 18 THEN 25 '.


Please help!

 

[dhookom]

MS doesn't support CASE WHEN syntax. Create a translation table and use it for all of your tables.

Duane
Hook'D on Access
MS Access MVP

 

[cumap]

I found a fix for the query on the above (similar to Duane mentioned). In case anyone interested, here is the fix:

UPDATE RMA " _
& " SET UserID = " _
& "	IIF(UserID=1, 9, " _
& " IIF(UserID=6, 12, " _
& " IIF(UserID=7, 19, " _
& " IIF(UserID=8, 16, " _
& " IIF(UserID=9, 14, " _
& " IIF(UserID=10, 17, " _
& " IIF(UserID=12, 15, " _
& " IIF(UserID=13, 18, " _
& " IIF(UserID=14, 21, " _
& " IIF(UserID=15, 22, " _
& " IIF(UserID=16, 23, " _
& " IIF(UserID=17, 24, " _
& " IIF(UserID=18, 25, " _
& " IIF(UserID=19, 16)))))))))))))) " _
& "WHERE UserID IN (1,6,7,8,9,10,12,13,14,15,16,17,18,19)

peace!!!

 

[dhookom]

IMO, that solution is far from ideal. I would have created a table with two fields and run an update query.

Duane
Hook'D on Access
MS Access MVP