Derived Table Error in Update query

[JSMITH242B]

Hi Group,
I've issued the following update query:

update _smdba_.[Purchase Requests]
set [One Off Account Code ID]= [Main Account Code ID]
WHERE [Req #] = 20049

I want to update the One Off Account Code ID with the Main Account Code ID.

However, I get the following error:
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'Purchase Requests' is not updatable because a column of the derived table is derived or constant.

Does anyone know why this error occurs and if so how can I achieve what I'm trying to do?

I need to issue this update for more than a 1000 records.

Kind Regards

 

[JamesLean]

Is that the actual statement you are trying to execute? If so, can you post details of the [Purchase Requests] table (ideally the CREATE TABLE script)?

--James

 

[JSMITH242B]

Hi James,
Yes, this is the actual statement I've executed. The [Purchase Requests] object is a view.

 

[JamesLean]

OK - can you post the CREATE VIEW statement?

--James

 

[JSMITH242B]

Here goes:
CREATE view "_SMDBA_"."Purchase Requests" as SELECT
BASE."SEQUENCE" as "Req #",
BASE."LASTMODIFIED" as "LastModified",
BASE."LASTUSER" as "LastUser",
BASE."NOTE" as "Note",
BASE."_GROUP_" as "Seq.Group",
BASE."WO#",
BASE."DESCRIPTION" as "Description",
BASE."STATUS_CHANGED" as "Status Changed",
BASE."HD#",
BASE."CLIENT" as "SeqClient",
BASE."DEPT" as "SeqDepartment",
BASE."OPEN_BY" as "SeqUser",
BASE."VENDOR" as "SeqVendor",
BASE."DATE OPEN" as "Open Date & Time",
BASE."ORIGINATOR" as "SeqOriginator",
BASE."SENT_TO" as "SeqAssignedTo",
BASE."STATUS" as "State:",
BASE."SEQ_COMPANY" as "Seq Company",
BASE."REQUIRED_ON" as "Date Required By",
BASE."TAX_%" as "Tax_%",
BASE."SHIPPING_COST" as "Shipping Cost",
BASE."_INACTIVE_:" as "InActive:",
BASE."SEQ_DELIVERYLOCATION" as "seq.deliverylocation",
BASE."SEQ_CHANGEREQ" as "seq.changereq",
BASE."SEQ_TCO" as "seq.tco",
BASE."SEQ_ACCOUNTCODES" as "seq.accountcodes",
BASE."SEQ_COSTCENTRE" as "seq.costcentre",
BASE."CLIENT_REQUIRED_DATE" as "Client Required Date",
BASE."SERVICES_ONLY" as "Services Only",
BASE."SEQ_CHGPRGENERATOR" as "seq.chgprgenerator",
BASE."PARENT_PREQ",
BASE."JUNEAU_REF" as "Juneau Ref",
BASE."INSTRUCTIONS" as "Instructions",
BASE."PRINT_INSTRUCTIONS" as "Print Instructions",
BASE."VENDOR_INSTRUCTIONS" as "Vendor Instructions",
BASE."PRINT_VENDOR_INSTRUCTIONS" as "Print Vendor Instructions",
BASE."SEQ_LEASE_CC" as "Seq.LeaseCostCentre",
BASE."SEQ_RECHARGE_CC" as "Seq.RechargeCostCentre",
BASE."SEQ_LEASE_AC" as "Seq.LeaseAccountCode",
BASE."SEQ_RECHARGE_AC" as "Seq.RechargeAccountCode",
BASE."SEQ_ONEOFF_CC" as "Seq.OneOffCostCentre",
BASE."SEQ_ONEOFF_AC" as "Seq.OneOffAccountCode",
BASE."SEQ_LEASE_PERIOD" as "Seq.LeasePeriod",
BASE."SEQ_LEASESUSP_CC" as "Seq.LeaseSuspenseCC",
BASE."SEQ_LEASESUSP_AC" as "Seq.LeaseSuspenseAC",
BASE."SEQ_LEASEPAY_CC" as "Seq.LeasePaymentCC",
BASE."SEQ_LEASEPAY_AC" as "Seq.LeasePaymentAC",
BASE."LOCKED_IND" as "Locked Ind",
BASE."DATE_LOCKED" as "Date Locked",
BASE."LOCK_NUMBER" as "Lock Number",
BASE."SEQ_LOCKED_BY_LOGIN_ID" as "Seq.Locked by Login ID",
BASE."SEQ_LOCKED_BY_GROUP" as "Seq.Locked by Group",
BASE."DATE_UNLOCKED" as "Date Unlocked",
BASE."SEQ_UNLOCKED_BY_LOGIN_ID" as "Seq.Unlocked by Login ID",
BASE."SEQ_UNLOCKED_BY_GROUP" as "Seq.Unlocked by Group",
((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD where CHILD."SEQ_PR" = BASE."SEQUENCE")) as "Subtotal",
(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD where CHILD."SEQ_PR" = BASE."SEQUENCE")*(1+(ISNULL(BASE."TAX_%",0)*0.01))+ISNULL(BASE."SHIPPING_COST",0))) as "Total Cost",
(((Select PROJ."PREFIX_CODE" from "_SMDBA_"."PROJCTPREFIX" PROJ Where PROJ."SEQUENCE" = (Select "SEQ_PROJPREFIX" from "_SMDBA_"."_CHANGE_" where "SEQUENCE" = base."SEQ_CHANGEREQ")) + '/' + (Select FYR."FINANCIAL_YEAR_ID" from "_SMDBA_"."FIN_YEAR" FYR Where FYR."SEQUENCE" = (Select "SEQ_FINANCIALYEAR" from "_SMDBA_"."_CHANGE_" where "SEQUENCE" = base."SEQ_CHANGEREQ")) + '/' + (CAST (base."SEQ_CHANGEREQ" as Varchar)))) as "CM Project Reference",
"J0"."CODE" as "Group Code",
"J1"."CLIENT" as "Client ID",
"J1"."FNAME" as "First Name",
"J1"."NAME" as "Last Name",
"J1"."CLIENT_TELEPHONE" as "Client Telephone",
"J1"."EXT" as "Client Ext",
"J2"."LOCATION_ID" as "Client Location ID",
"J2"."LOCATION_ADDRESS" as "Client Location Address",
"J3"."DEPARTMENT_NAME" as "LBHF Department Name",
"J2"."ADDRESS_ONE" as "Location Address One",
"J2"."ADDRESS_TWO" as "Location Address Two",
"J2"."ADDRESS_THREE" as "Location Address Three",
"J2"."ADDRESS_FOUR" as "Location Address Four",
"J2"."POST_CODE" as "Location Post Code",
"J4"."DEPT" as "Department ID",
"J4"."NAME" as "Department Name",
"J5"."CODE" as "Login ID Assigned To",
"J5"."FNAME" as "Assigned First Name",
"J5"."NAME" as "Assigned Last Name",
"J5"."STAFF_TELEPHONE" as "Assigned Telephone",
"J6"."ADDRESS_ONE" as "Staff Location Address One",
"J6"."ADDRESS_TWO" as "Staff Location Address Two",
"J6"."ADDRESS_THREE" as "Staff Location Address Three",
"J6"."ADDRESS_FOUR" as "Staff Location Address Four",
"J6"."POST_CODE" as "Staff Location Post Code",
"J5"."DISPLAY_EMAIL" as "Assigned to Email",
"J7"."ACCOUNT_CODE_ID" as "Main Account Code ID",
"J8"."PROJECT_REFERENCE" as "Project Reference",
"J8"."DESCRIPTION" as "Change Description",
"J9"."PREFIX_CODE" as "Project Prefix Code",
"J10"."FINANCIAL_YEAR_ID" as "Financial Year ID",
"J8"."SEQUENCE" as "CR No",
"J8"."JUNEAU_REFERENCE" as "Juneau Reference",
"J8"."PROJECT_TITLE" as "Project Title",
"J11"."NAME" as "Company Name",
"J11"."CODE" as "Company ID",
"J12"."COST_CENTRE_ID" as "Main Cost Centre ID",
"J13"."ADDRESS_TWO" as "Delivery Address Two",
"J13"."ADDRESS_THREE" as "Delivery Address Three",
"J13"."ADDRESS_FOUR" as "Delivery Address Four",
"J13"."POST_CODE" as "Delivery Post Code",
"J13"."LOCATION_ID" as "Delivery Location ID",
"J13"."ADDRESS_ONE" as "Delivery Address One",
"J13"."LOCATION_ADDRESS" as "Delivery Location Address",
"J14"."ACCOUNT_CODE_ID" as "Lease Account Code ID",
"J15"."COST_CENTRE_ID" as "Lease Cost Centre ID",
"J16"."LEASE_TERM" as "Lease Term",
"J17"."FINANCIAL_YEAR_ID" as "Lease Financial Year ID",
"J18"."ACCOUNT_CODE_ID" as "Lease Payment ACID",
"J19"."COST_CENTRE_ID" as "Lease Payment CCID",
"J20"."ACCOUNT_CODE_ID" as "Lease Suspense ACID",
"J21"."COST_CENTRE_ID" as "Lease Suspense CCID",
"J22"."CODE" as "Locked by Group ID",
"J23"."CODE" as "Locked by Login ID",
"J23"."FNAME" as "Locked by Login ID Fname",
"J23"."NAME" as "Locked by Login ID Lname",
"J24"."ACCOUNT_CODE_ID" as "One Off Account Code ID",
"J25"."COST_CENTRE_ID" as "One Off Cost Centre ID",
"J26"."ACCOUNT_CODE_ID" as "Recharge Account Code ID",
"J27"."COST_CENTRE_ID" as "Recharge Cost Centre ID",
"J28"."DESCRIPTION" as "TCO Description",
"J29"."CODE" as "Unlocked by Group ID",
"J30"."CODE" as "Unlocked by Login ID",
"J30"."FNAME" as "Unlocked by Login ID Fname",
"J30"."NAME" as "Unlocked by Login ID Lname",
"J31"."CODE" as "Status ID",
"J32"."ADDRESS" as "Vendor Address",
"J32"."FAX_NUMBER" as "Vendor Fax Number",
"J32"."NAME" as "Vendor Name",
"J32"."CODE" as "Vendor ID",
"J32"."TELPHONE" as "Vendor Telephone"
FROM "_SMDBA_"."_PR_" as BASE
left outer join "_SMDBA_"."_GROUPS_" as "J0" ON "J0"."SEQUENCE" = BASE."_GROUP_"
left outer join "_SMDBA_"."_CUSTOMER_" as "J1" ON "J1"."SEQUENCE" = BASE."CLIENT"
left outer join "_SMDBA_"."LOCATION" as "J2" ON "J2"."SEQUENCE" = J1."SEQ_LOCATIONS"
left outer join "_SMDBA_"."LBHFDEPART" as "J3" ON "J3"."SEQUENCE" = J1."SEQ_LBHFDEPARTMENTS"
left outer join "_SMDBA_"."_DEPART_" as "J4" ON "J4"."SEQUENCE" = BASE."DEPT"
left outer join "_SMDBA_"."_PERSONNEL_" as "J5" ON "J5"."SEQUENCE" = BASE."SENT_TO"
left outer join "_SMDBA_"."LOCATION" as "J6" ON "J6"."SEQUENCE" = J5."SEQ_LOCATION"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J7" ON "J7"."SEQUENCE" = BASE."SEQ_ACCOUNTCODES"
left outer join "_SMDBA_"."_CHANGE_" as "J8" ON "J8"."SEQUENCE" = BASE."SEQ_CHANGEREQ"
left outer join "_SMDBA_"."PROJCTPREFIX" as "J9" ON "J9"."SEQUENCE" = J8."SEQ_PROJPREFIX"
left outer join "_SMDBA_"."FIN_YEAR" as "J10" ON "J10"."SEQUENCE" = J8."SEQ_FINANCIALYEAR"
left outer join "_SMDBA_"."_COMPANY_" as "J11" ON "J11"."SEQUENCE" = BASE."SEQ_COMPANY"
left outer join "_SMDBA_"."COSTCENTRE" as "J12" ON "J12"."SEQUENCE" = BASE."SEQ_COSTCENTRE"
left outer join "_SMDBA_"."LOCATION" as "J13" ON "J13"."SEQUENCE" = BASE."SEQ_DELIVERYLOCATION"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J14" ON "J14"."SEQUENCE" = BASE."SEQ_LEASE_AC"
left outer join "_SMDBA_"."COSTCENTRE" as "J15" ON "J15"."SEQUENCE" = BASE."SEQ_LEASE_CC"
left outer join "_SMDBA_"."LEASE_PERIOD" as "J16" ON "J16"."SEQUENCE" = BASE."SEQ_LEASE_PERIOD"
left outer join "_SMDBA_"."FIN_YEAR" as "J17" ON "J17"."SEQUENCE" = J16."SEQ_FIN_YEAR"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J18" ON "J18"."SEQUENCE" = BASE."SEQ_LEASEPAY_AC"
left outer join "_SMDBA_"."COSTCENTRE" as "J19" ON "J19"."SEQUENCE" = BASE."SEQ_LEASEPAY_CC"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J20" ON "J20"."SEQUENCE" = BASE."SEQ_LEASESUSP_AC"
left outer join "_SMDBA_"."COSTCENTRE" as "J21" ON "J21"."SEQUENCE" = BASE."SEQ_LEASESUSP_CC"
left outer join "_SMDBA_"."_GROUPS_" as "J22" ON "J22"."SEQUENCE" = BASE."SEQ_LOCKED_BY_GROUP"
left outer join "_SMDBA_"."_PERSONNEL_" as "J23" ON "J23"."SEQUENCE" = BASE."SEQ_LOCKED_BY_LOGIN_ID"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J24" ON "J24"."SEQUENCE" = BASE."SEQ_ONEOFF_AC"
left outer join "_SMDBA_"."COSTCENTRE" as "J25" ON "J25"."SEQUENCE" = BASE."SEQ_ONEOFF_CC"
left outer join "_SMDBA_"."ACCOUNTCODE" as "J26" ON "J26"."SEQUENCE" = BASE."SEQ_RECHARGE_AC"
left outer join "_SMDBA_"."COSTCENTRE" as "J27" ON "J27"."SEQUENCE" = BASE."SEQ_RECHARGE_CC"
left outer join "_SMDBA_"."_STDCFG_" as "J28" ON "J28"."SEQUENCE" = BASE."SEQ_TCO"
left outer join "_SMDBA_"."_GROUPS_" as "J29" ON "J29"."SEQUENCE" = BASE."SEQ_UNLOCKED_BY_GROUP"
left outer join "_SMDBA_"."_PERSONNEL_" as "J30" ON "J30"."SEQUENCE" = BASE."SEQ_UNLOCKED_BY_LOGIN_ID"
left outer join "_SMDBA_"."_PURCHSTAT_" as "J31" ON "J31"."SEQUENCE" = BASE."STATUS"
left outer join "_SMDBA_"."_VENDORS_" as "J32" ON "J32"."SEQUENCE" = BASE."VENDOR"

 

[JamesLean]

Sorry I asked... ;-)

As the error says, the view is not updateable because it contains derived columns (eg SubTotal).

From looking at the tables involved you should be able to perform your update using something like this:

UPDATE J24
SET ACCOUNT_CODE_ID = J7.ACCOUNT_CODE_ID
FROM [_SMDBA_].[_PR_] AS BASE
  JOIN [_SMDBA_].ACCOUNTCODE AS J24 ON BASE.SEQ_ONEOFF_AC = J24.SEQUENCE
  JOIN [_SMDBA_].ACCOUNTCODE AS J7 ON BASE.SEQ_ACCOUNTCODES = J7.SEQUENCE

--James

 

[JSMITH242B]

Hi James,
I'll give that a go.

cheers