Avatar of amankhan2005
Flag for India asked on

Oracle SQL Update Issue

  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.

Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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.

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>

Sorry for the typo, OSP-47281 should be OSP-47381
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck