Can not update records

  • I tried running an Update staement and I get "Can not update multiple records." I am trying to set one field to 'GINA' and I am using two where statements.  Does anyone know of anyway around this?

  • Do you have a primary key on the table?

  • I found out that there are 2 other fields that need to be filled in when you enter info in the other. I also know that Update statements can only do one field at a time. What to do?

  • Post the code you are using... you can update only from one table at the time, but you can update all the fields at once if needed.

  • update procure_det set vendor_no = 'GINALEVAN' where vendor_no is NULL and source_code = 'A'

     

    There are three other fields that need to have data. remit_to = '1' buy_from = '1' gtc_code = '1'

     

    The buy_from and remit_to are required with setting the vendor_no = 'GINALEVAN'

  • update procure_det set

    vendor_no = 'GINALEVAN',

    remit_to = '1',

    buy_from = '1',

    gtc_code = '1'

    where vendor_no is NULL and source_code = 'A'

    does this work?

    And do you have a primary key.

  • No. That did not work. I don't where or how to look for a primary key.

  • run this in query analyser :

    exec sp_helpindex 'procure_det'

    check for primary key, clustered in the description

    Also are you trying to update a table or something else?

  • Here are my results. Procure_det is a table.

     

    id1_procure_det_resource_no

    clustered, unique located on PRIMARY resource_no id2_procure_det_source_code

    nonclustered located on PRIMARY source_code id3_procure_det_commodity

    nonclustered located on PRIMARY commodity id4_procure_det_product_class

    nonclustered located on PRIMARY product_class id5_procure_det_planner

    nonclustered located on PRIMARY planner 

  • Can you post the table definition, sampla data (insert scripts ONLY). Repost the update query that fails (make sure that the update fails with the data you send me). If I can recreate the problem I'll probabely be able to solve it.

  • update procure_det set

    vendor_no = 'GINALEVAN',

    remit_to = '1',

    buy_from = '1',

    gtc_code = '1'

    where vendor_no is NULL and source_code = 'A'

    exec sp_helpindex 'procure_det'

     

    Results: Server: Msg 21359, Level 16, State 1, Procedure rsp_rms_errors, Line 109

    21359: Can not update multiple records on Procure_det.  () update not allowed

     

     

    how can I get the table definition easily? The table is pretty big. (lots of cloumns).

  • Help us help you

  • CREATE TABLE [procure_det] (

     [resource_no] [rsc_dt] NOT NULL ,

     [source_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [source_cntl] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [abc_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [restrict_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [product_class] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [bom_note] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [planner] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [mrp_review] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [order_policy] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [order_qty] [float] NULL ,

     [order_qty_inc] [float] NULL ,

     [order_qty_min] [float] NULL ,

     [order_qty_brk] [float] NULL ,

     [lead_time] [smallint] NULL ,

     [lead_time_unit] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [buy_from] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [vendor_no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [remit_to] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [account] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [date_changed] [datetime] NULL ,

     [floor_stock] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [annual_forecast] [float] NULL ,

     [cycle_cost] [float] NULL ,

     [prime_work_ctr] [rsc_dt] NULL ,

     [buyer] [buy_dt] NULL ,

     [manf_engr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [safety_stock] [float] NULL ,

     [mfg_lead_time] [float] NULL ,

     [mfg_lead_unit] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [phantom_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [shelf_life] [smallint] NULL ,

     [gtc_code] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [acct_clerk] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [cost_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [commodity] [comm_dt] NULL ,

     [low_level] [int] NULL ,

     [shelf_life_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [admin_lead_time] [float] NULL ,

     [admin_lead_time_unit] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [insp_lead_time] [float] NULL ,

     [insp_lead_time_unit] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [use_up_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [std_lot_size] [int] NULL ,

     [ecn_control_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [attribute_controlled_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [serial_method] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [serial_format_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [lot_method] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [lot_format_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [taxable_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [tax_type] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [apply_to] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

     

    Resource NoSource CodeSource CntlAbc CodeRestrict CodeProduct ClassBom NotePlannerMrp ReviewOrder PolicyOrder QtyOrder Qty IncOrder Qty MinOrder Qty BrkLead TimeLead Time UnitBuy FromVendor NoRemit ToAccountDate ChangedFloor StockAnnual ForecastCycle CostPrime Work CtrBuyerManf EngrSafety StockMfg Lead TimeMfg Lead UnitPhantom CodeShelf LifeGtc CodeAcct ClerkCost CodeCommodityLow LevelShelf Life TypeAdmin Lead TimeAdmin Lead Time UnitInsp Lead TimeInsp Lead Time UnitUse Up CodeStd Lot SizeEcn Control FlagAttribute Controlled FlagSerial MethodSerial Format FlagLot MethodLot Format FlagTaxable FlagTax TypeApply To

    010508LA-CABC50115Y11,000.0DY1KECOINC115500.01Y55MD1NNNN
  • Do you really expect me to type that myself in the db.. the link tells you how to generate the insert statements.

    Also I forgot to tell you to include the indexes and constraints.

  • Thanks you for your time but I am out of here. Have a great weekend.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply