Query update multiple single values with new single value 2

[Brenj68]

Hi, I'm trying to get this to work (as a bulk update). I'm using Ora 10 via SQL Developer

UPDATE mun474t.odf_ca_project
SET dh_product_2 = 'Adhoc insights'
WHERE EXISTS (dh_product_2 = 'CI - Brand Loyalty Activator');,
'CI - Brand Loyalty Analysis/Migration',
'CI - Brand Loyalty Lab',
'CI - Buyer Flow',
'CI - Client Database/Panel Match & Analysis',
'CI - Client Database/Panel Match Only',
'CI - Custom Behavioral Segmentation Analysis',
'CI - Custom Latino/Hispanic Analysis',
'CI - Custom Panel Creation',
'CI - Integrated Category Analysis',
'CI - Integrated Segmentation Analysis',
'CI - Market Structure',
'CI - Multiple Product/Service Solution',
'CI - Other Integrated Analysis',
'CI - Other Shelf Analysis',
'CI - Sample/Demo Analysis',
'CI - Shopper Profiling',
'CI - Test/Control',
'CI - Test/Control HH Analysis Only',
'CI - Test/Control HH Match & Analysis',
'CI - Test/Control HH Match Only',
'Initiatives - c-Store',
'Initiatives - ROM',
'Insights - Behavior Segmentation (not specific)',
'Insights - Brand Loyalty Analysis',
'Insights - Consumer Assortment Suite',
'Insights - Consumer Needs States (Behavior & Attitude Studies)',
'Insights - Consumer Profiling',
'Insights - Consumer Promotion Suite',
'Insights - Core Consumer Guardrails',
'Insights - CRM Program Effectiveness',
'Insights - Custom',
'Insights - Market Structure',
'Insights - Other',
'Insights - Pricing Analysis',
'Internal - Other / Non-Product')

It works fine if I do this (product update) individually, but I get ORA00928 - missing select keyword. This is not my normal function, just something I need to do as part of something bigger, so any help wld b a big bonus. Thanks in advance.

B

 

[SantaMufasa]

Rather than just get your syntax to work, I'll opt for getting your SQL to do what you want. It appears to me that you want to replace dh_product_2's value with "Adhoc insights" if dh_product_2's current value is one of the 36 you listed. Right? (If my inferrence is flawed, please correct me as soon as possible.)

So, if I am correct, you can accomplish your objective with this code:

UPDATE mun474t.odf_ca_project
   SET dh_product_2 = 'Adhoc insights'
 WHERE dh_product_2
       in ('CI - Brand Loyalty Activator')
          ,'CI - Brand Loyalty Analysis/Migration'
          ,'CI - Brand Loyalty Lab'
          ,'CI - Buyer Flow'
          ,'CI - Client Database/Panel Match & Analysis'
          ,'CI - Client Database/Panel Match Only'
          ,'CI - Custom Behavioral Segmentation Analysis'
          ,'CI - Custom Latino/Hispanic Analysis'
          ,'CI - Custom Panel Creation'
          ,'CI - Integrated Category Analysis'
          ,'CI - Integrated Segmentation Analysis'
          ,'CI - Market Structure'
          ,'CI - Multiple Product/Service Solution'
          ,'CI - Other Integrated Analysis'
          ,'CI - Other Shelf Analysis'
          ,'CI - Sample/Demo Analysis'
          ,'CI - Shopper Profiling'
          ,'CI - Test/Control'
          ,'CI - Test/Control HH Analysis Only'
          ,'CI - Test/Control HH Match & Analysis'
          ,'CI - Test/Control HH Match Only'
          ,'Initiatives - c-Store'
          ,'Initiatives - ROM'
          ,'Insights - Behavior Segmentation (not specific)'
          ,'Insights - Brand Loyalty Analysis'
          ,'Insights - Consumer Assortment Suite'
          ,'Insights - Consumer Needs States (Behavior & Attitude Studies)'
          ,'Insights - Consumer Profiling'
          ,'Insights - Consumer Promotion Suite'
          ,'Insights - Core Consumer Guardrails'
          ,'Insights - CRM Program Effectiveness'
          ,'Insights - Custom'
          ,'Insights - Market Structure'
          ,'Insights - Other'
          ,'Insights - Pricing Analysis'
          ,'Internal - Other / Non-Product');

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Sorry, Brenj68, I didn't run the code against my Oracle (nor have I yet), but I noticed a flaw that came across during my copy-and-paste of your code...(I left in an extraneous right paren.) Here is the amended code:

UPDATE mun474t.odf_ca_project
   SET dh_product_2 = 'Adhoc insights'
 WHERE dh_product_2
       in ('CI - Brand Loyalty Activator'
          ,'CI - Brand Loyalty Analysis/Migration'
          ,'CI - Brand Loyalty Lab'
          ,'CI - Buyer Flow'
          ,'CI - Client Database/Panel Match & Analysis'
          ,'CI - Client Database/Panel Match Only'
          ,'CI - Custom Behavioral Segmentation Analysis'
          ,'CI - Custom Latino/Hispanic Analysis'
          ,'CI - Custom Panel Creation'
          ,'CI - Integrated Category Analysis'
          ,'CI - Integrated Segmentation Analysis'
          ,'CI - Market Structure'
          ,'CI - Multiple Product/Service Solution'
          ,'CI - Other Integrated Analysis'
          ,'CI - Other Shelf Analysis'
          ,'CI - Sample/Demo Analysis'
          ,'CI - Shopper Profiling'
          ,'CI - Test/Control'
          ,'CI - Test/Control HH Analysis Only'
          ,'CI - Test/Control HH Match & Analysis'
          ,'CI - Test/Control HH Match Only'
          ,'Initiatives - c-Store'
          ,'Initiatives - ROM'
          ,'Insights - Behavior Segmentation (not specific)'
          ,'Insights - Brand Loyalty Analysis'
          ,'Insights - Consumer Assortment Suite'
          ,'Insights - Consumer Needs States (Behavior & Attitude Studies)'
          ,'Insights - Consumer Profiling'
          ,'Insights - Consumer Promotion Suite'
          ,'Insights - Core Consumer Guardrails'
          ,'Insights - CRM Program Effectiveness'
          ,'Insights - Custom'
          ,'Insights - Market Structure'
          ,'Insights - Other'
          ,'Insights - Pricing Analysis'
          ,'Internal - Other / Non-Product');

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Brenj68]

SantaMufasa,

Big thanks worked as expected/ hoped. Cheers

 

[carp]

Santa Mufasa -
Since Brenj68 forgot that a "big thanks" ought to be accompanied by a purple star, please allow me to be the first!

 

[SantaMufasa]

As you have always been during the nearly 20 years I have known you, you are very kind !

Happy Holidays,


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.