Oracle SQL Update Issue

amankhan2005
amankhan2005 used Ask the Experts™
on
Hi,
 
  I am having a small issue with writing an update statement...


SELECT inventory_item_id, organization_id, summary_flag, enabled_flag,
       description, invoicing_rule_id, segment1, attribute1, attribute2,
       purchasing_item_flag, shippable_item_flag, customer_order_flag,
       internal_order_flag, service_item_flag, inventory_item_flag,
       eng_item_flag, inventory_asset_flag, purchasing_enabled_flag,
       customer_order_enabled_flag, internal_order_enabled_flag,
       so_transactions_flag, mtl_transactions_enabled_flag,
       stock_enabled_flag, bom_enabled_flag, build_in_wip_flag,
       revision_qty_control_code, item_catalog_group_id, catalog_status_flag,
       returnable_flag, qty_rcv_exception_code, allow_item_desc_update_flag,
       inspection_required_flag, receipt_required_flag, market_price,
       qty_rcv_tolerance, list_price_per_unit, price_tolerance_percent,
       unit_of_issue, enforce_ship_to_location_code,
       allow_substitute_receipts_flag, allow_unordered_receipts_flag,
       allow_express_delivery_flag, days_early_receipt_allowed,
       days_late_receipt_allowed, receipt_days_exception_code,
       receiving_routing_id, invoice_close_tolerance, receive_close_tolerance,
       auto_lot_alpha_prefix, start_auto_lot_number, lot_control_code,
       shelf_life_code, shelf_life_days, serial_number_control_code,
       start_auto_serial_number, auto_serial_alpha_prefix, source_type,
       expense_account, encumbrance_account, unit_weight, weight_uom_code,
       restrict_locators_code, location_control_code,
       planning_time_fence_code, lead_time_lot_size, acceptable_rate_increase,
       acceptable_rate_decrease, cumulative_total_lead_time,
       planning_time_fence_days, end_assembly_pegging_flag,
       repetitive_planning_flag, bom_item_type, pick_components_flag,
       replenish_to_order_flag, atp_components_flag, atp_flag,
       fixed_lead_time, wip_supply_type, wip_supply_subinventory,
       primary_uom_code, primary_unit_of_measure, allowed_units_lookup_code,
       cost_of_sales_account, sales_account, default_include_in_rollup_flag,
       inventory_item_status_code, inventory_planning_code,
       planning_make_buy_code, rounding_control_type,
       postprocessing_lead_time, full_lead_time, mrp_safety_stock_code,
       min_minmax_quantity, max_minmax_quantity, reservable_type,
       vendor_warranty_flag, serviceable_component_flag,
       serviceable_product_flag, preventive_maintenance_flag,
       prorate_service_flag, invoiceable_item_flag, invoice_enabled_flag,
       must_use_approved_vendor_flag, outside_operation_flag,
       outside_operation_uom_type, costing_enabled_flag,
       auto_created_config_flag, cycle_count_enabled_flag, item_type,
       ship_model_complete_flag, mrp_planning_code,
       return_inspection_requirement, ato_forecast_control,
       release_time_fence_code, effectivity_control, check_shortages_flag,
       equipment_type, comms_nl_trackable_flag, asset_creation_code,
       web_status, dimension_uom_code, unit_length, unit_width,
       bulk_picked_flag, lot_status_enabled, serial_status_enabled,
       lot_split_enabled, lot_merge_enabled, dual_uom_control,
       dual_uom_deviation_high, dual_uom_deviation_low, serv_req_enabled_code,
       serv_billing_enabled_flag, lot_translate_enabled,
       default_so_source_type, create_supply_flag, lot_substitution_enabled,
       object_version_number, tracking_quantity_ind, ont_pricing_qty_source,
       so_authorization_flag, consigned_flag, asn_autoexpire_flag,
       vmi_forecast_type, exclude_from_budget_flag, drp_planned_flag,
       critical_component_flag, continous_transfer, convergence, divergence
  FROM mtl_system_items_b
 WHERE organization_id = 361
 and segment1 ='OSP-47380'
 order by last_update_date desc

  In the above, I have an item and have selected columns in the select statement....

I have another item OSP-47381 and want to update the values from the above select columns which are set for OSP-47380 item.

 Instead of writing update statement and set column values manually and selecting each column and setting it to the value from the above displayed select statement for OSP-47380 Item...

  Update mtl_system_items_b
     set   attribute1 = 'Yes',
             attribute2 = 'No',
.
.
.
.
.
.
.
.
.
.
..
..

   where organization_id = 361
      and segment1 = 'OSP-47381'
           
  Help Appreciated.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
you can use MERGE:

MERGE
INTO  tableA updatetab
USING  (
       SELECT  *
       FROM    tableB
       WHERE   columname=<value>
       ) sourcetab
ON  (updatetab.columname=<value>)
WHEN MATCHED THEN
UPDATE
SET  columnameA=sourcetab.columnnameA,
       columnameB=sourcetab.columnnameB,
       columnameC=sourcetab.columnnameC

Commented:
Hello,
which are the columns where you two selects match?
you could use the Merge command as given b OP_Zaharin or make a procedure with a loop or make an update with a select as values
update table set (value1, value2 )=(select newvalue1,newvalue2 from atable where how they join)
where what should exist
hope it helps.
awking00Information Technology Specialist

Commented:
delete from  mtl_system_items_b where segment = 'OSP-47381';
insert into  mtl_system_items_b
SELECT inventory_item_id, organization_id, summary_flag, enabled_flag,
       description, invoicing_rule_id, replace(segment1,'OSP-47380','OSP-47281'), attribute1, attribute2,... <THE REST OF YOUR SELECT STATEMENT>
awking00Information Technology Specialist

Commented:
Sorry for the typo, OSP-47281 should be OSP-47381

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial