Update field (multiple criteria and updates)

  • Thread starter Thread starter ksh
  • Start date Start date
K

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*"));"
 
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
 
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";
 
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.

John Spencer said:
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*"));"
Click to expand...
.
Click to expand...
 
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*"));"
Click to expand...

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".
 
John W. Vinson said:
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*"));"
Click to expand...

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".
Click to expand...

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.
 
Back
Top