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).
-
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 No Source Code Source Cntl Abc Code Restrict Code Product Class Bom Note Planner Mrp Review Order Policy Order Qty Order Qty Inc Order Qty Min Order Qty Brk Lead Time Lead Time Unit Buy From Vendor No Remit To Account Date Changed Floor Stock Annual Forecast Cycle Cost Prime Work Ctr Buyer Manf Engr Safety Stock Mfg Lead Time Mfg Lead Unit Phantom Code Shelf Life Gtc Code Acct Clerk Cost Code Commodity Low Level Shelf Life Type Admin Lead Time Admin Lead Time Unit Insp Lead Time Insp Lead Time Unit Use Up Code Std Lot Size Ecn Control Flag Attribute Controlled Flag Serial Method Serial Format Flag Lot Method Lot Format Flag Taxable Flag Tax Type Apply To 010508LA-C A B C 501 15 Y 1 1,000. 0 DY 1 KECOINC 1 15 500. 01 Y 55MD 1 N N N N -
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