Update on conditions.

[Richard U]

I'm cleaning up a database that had inconsistent entries in a particular column.

I know I can run multiple updates like this

UPDATE temp SET [Shelf Name] = "SHELF 1"
Where [Shelf Name] = "SHELF1"

UPDATE temp SET [Shelf Name] = "SHELF 2"
Where [Shelf Name] = "SHELF2"

UPDATE temp SET [Shelf Name] = "SHELF 3"
Where [Shelf Name] = "SHELF3"

and so on.

Is there a way in access where I can do this in one query?

(sorry, very rusty in access)

 

[chrism216]

I'm cleaning up a database that had inconsistent entries in a particular column.

I know I can run multiple updates like this

UPDATE temp SET [Shelf Name] = "SHELF 1"
Where [Shelf Name] = "SHELF1"

UPDATE temp SET [Shelf Name] = "SHELF 2"
Where [Shelf Name] = "SHELF2"

UPDATE temp SET [Shelf Name] = "SHELF 3"
Where [Shelf Name] = "SHELF3"

and so on.

Is there a way in access where I can do this in one query?

(sorry, very rusty in access)

Theres a pretty cool way of doing this with a combination of these two:

INSERT INTO
ON DUPLICATE KEY UPDATE

You should read up on that.

Note: this method is especially useful as you can use it to both update AND insert records.

An example on Stack Overflow http://stackoverflow.com/questions/...tiple-rows-with-different-values-in-one-query

<code style="margin: **** padding: **** border: **** font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">INSERT INTO table_users (cod_user, date, user_rol, cod_office)
VALUES
('622057','12082014','student','123456'),
('2913659','12082014','assistant','123456'),
('6160230','12082014','admin','123456')
ON DUPLICATE KEY UPDATE
cod_user=VALUES(cod_user), date=VALUES(date)</code>


Cheers,

Chris

 

[Joe4]

Try this:

UPDATE temp
SET [Shelf Name] = "SHELF " & Right([Shelf Name],1)
WHERE temp.[Shelf Name] In ("SHELF1","SHELF2","SHELF3");

 

[Richard U]

Thanks all!