Update field (multiple criteria and updates)

[ksh]

I would like to update 2 fields with the following
Where [item description] like "*BASIC*" update [item description] = "BASIC"
and [item] = "HITS"
Where [item description] like "*FARE*" update [item description] = "FARE"
and [item] = "HITS"
Where [item description] like "*SEARCH*" update [item description] =
"SEARCH" and [item] = "HITS"

I typically use design view to set up queries and I can only do one of the
three changes in this view. Is there are way to write this in sql to do all
three updates at the same time?

This is the sql query based on my entry in design view for the first update.
"UPDATE [Q-CHARGES] SET [Q-CHARGES].[ITEM DESCRIPTION] = "BASIC",
[Q-CHARGES].ITEM = "HITS"
WHERE ((([Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"));"

 

[John Spencer]

You can use a nested IIF statement when updating Item Description.
UPDATE [Q-CHARGES]
SET [Q-CHARGES].[ITEM DESCRIPTION] =
IIF([ITEM DESCRIPTION]) Like "*basic*","BASIC",
IIF([ITEM DESCRIPTION]) Like "*Fare*","FARE",
IIF([ITEM DESCRIPTION]) Like "*Search*","SEARCH",
[ITEM DESCRIPTION])))
, [Q-CHARGES].ITEM = "HITS"
WHERE [Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"
OR [Q-CHARGES].[ITEM DESCRIPTION]) Like "*Fare*"
OR [Q-CHARGES].[ITEM DESCRIPTION]) Like "*Search*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Try this --
UPDATE [Q-CHARGES] SET [Q-CHARGES].[ITEM DESCRIPTION] =
IIF(([Q-CHARGES].[ITEM DESCRIPTION] Like "*basic*", "BASIC",
IIF([Q-CHARGES].[ITEM DESCRIPTION] Like "*FARE*", "FARE",
IIF([Q-CHARGES].[ITEM DESCRIPTION] Like "*SEARCH*", "SEARCH",
[Q-CHARGES].[ITEM DESCRIPTION])))
WHERE [Q-CHARGES].ITEM = "HITS";

 

[ksh]

Thank you both! The second solution worked since it also updated the item
field based on the basic fare and search criteria. What I was doing in three
separate queries I can now do in 1. This forum is the best learning tool I
know of, thank you again.

You can use a nested IIF statement when updating Item Description.
UPDATE [Q-CHARGES]
SET [Q-CHARGES].[ITEM DESCRIPTION] =
IIF([ITEM DESCRIPTION]) Like "*basic*","BASIC",
IIF([ITEM DESCRIPTION]) Like "*Fare*","FARE",
IIF([ITEM DESCRIPTION]) Like "*Search*","SEARCH",
[ITEM DESCRIPTION])))
, [Q-CHARGES].ITEM = "HITS"
WHERE [Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"
OR [Q-CHARGES].[ITEM DESCRIPTION]) Like "*Fare*"
OR [Q-CHARGES].[ITEM DESCRIPTION]) Like "*Search*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I would like to update 2 fields with the following
Where [item description] like "*BASIC*" update [item description] = "BASIC"
and [item] = "HITS"
Where [item description] like "*FARE*" update [item description] = "FARE"
and [item] = "HITS"
Where [item description] like "*SEARCH*" update [item description] =
"SEARCH" and [item] = "HITS"

I typically use design view to set up queries and I can only do one of the
three changes in this view. Is there are way to write this in sql to do all
three updates at the same time?

This is the sql query based on my entry in design view for the first update.
"UPDATE [Q-CHARGES] SET [Q-CHARGES].[ITEM DESCRIPTION] = "BASIC",
[Q-CHARGES].ITEM = "HITS"
WHERE ((([Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"));"

.

 

[John W. Vinson]

I would like to update 2 fields with the following
Where [item description] like "*BASIC*" update [item description] = "BASIC"
and [item] = "HITS"
Where [item description] like "*FARE*" update [item description] = "FARE"
and [item] = "HITS"
Where [item description] like "*SEARCH*" update [item description] =
"SEARCH" and [item] = "HITS"

I typically use design view to set up queries and I can only do one of the
three changes in this view. Is there are way to write this in sql to do all
three updates at the same time?

This is the sql query based on my entry in design view for the first update.
"UPDATE [Q-CHARGES] SET [Q-CHARGES].[ITEM DESCRIPTION] = "BASIC",
[Q-CHARGES].ITEM = "HITS"
WHERE ((([Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"));"

Just be careful, and as Karl says, *back up*: think about what will happen if
the item description were to contain "Whitsunday" or "unsearchable" or "this
is basically the same as Item 123".

 

[ksh]

I would like to update 2 fields with the following
Where [item description] like "*BASIC*" update [item description] = "BASIC"
and [item] = "HITS"
Where [item description] like "*FARE*" update [item description] = "FARE"
and [item] = "HITS"
Where [item description] like "*SEARCH*" update [item description] =
"SEARCH" and [item] = "HITS"

I typically use design view to set up queries and I can only do one of the
three changes in this view. Is there are way to write this in sql to do all
three updates at the same time?

This is the sql query based on my entry in design view for the first update.
"UPDATE [Q-CHARGES] SET [Q-CHARGES].[ITEM DESCRIPTION] = "BASIC",
[Q-CHARGES].ITEM = "HITS"
WHERE ((([Q-CHARGES].[ITEM DESCRIPTION]) Like "*basic*"));"

Just be careful, and as Karl says, *back up*: think about what will happen if
the item description were to contain "Whitsunday" or "unsearchable" or "this
is basically the same as Item 123".

Thank you for the excellent advice. I used a linked table from another db
to create a new table so I wouldn't change the original data and I regularly
back up both databases.