asked
Convert Select statement into Update statement
Hello Everyone I have a **Select Statement**, how could I convert this to an **Update Statement** please where I only want to update the "per.[PERSONNAME]" with a new name. Please see below for the Update Statement: SELECT DISTINCT ref.[REQUISITIONID] as 'Requisition ID', CONVERT(VARCHAR(128), DATEADD(HOUR,2,ref.[SUBMITTEDDATETIME]),20) as 'Date Submitted', ser.[SERVICENAME] as 'Service Name', ini.[PERSONNAME] as 'Initiator', ini.[LOGINNAME] as 'Initiator LoginID', cus.[PERSONNAME] as 'Customer', cus.[LOGINNAME] as 'Customer LoginID', dtf.[TASKNAME] as 'Task Name', dtf.[TASKSTATUS] as 'Task Status', CONVERT(VARCHAR(128), DATEADD(HOUR,2,dtf.[STARTEDDATETIME]),20) as 'Task Started Date', CONVERT(VARCHAR(128), DATEADD(HOUR,2,dtf.[COMPLETEDDATETIME]),20) as 'Task Completed Date', per.[PERSONNAME] as 'Service Owner', DATEDIFF(day, dtf.STARTEDDATETIME,dtf.COMPLETEDDATETIME) as 'Duration in Days' FROM [Datamart].[dbo].[DM_REQUISITIONENTRYFACT] ref INNER JOIN [Datamart].[dbo].[DM_AUTHTASKFACT] dtf ON ref.[REQUISITIONID] = dtf.[REQUISITIONID] INNER JOIN [Datamart].[dbo].[V_DM_SERVICE] ser ON ref.[SERVICEID] = ser.[SERVICEID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] ini ON ref.[REQUESTORID] = ini.[PERSONID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] cus ON ref.[CUSTOMERID] = cus.[PERSONID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] per ON dtf.[PERFORMERID] = per.[PERSONID] WHERE dtf.[STARTEDDATETIME] BETWEEN '2016-08-01' AND '2016-09-21' AND ser.[SERVICENAME] = 'Lync' AND ref.[SERVICEREQUESTSTATUS] = 'Completed' AND (dtf.[TASKNAME] LIKE 'Service Owner Approval%' OR dtf.[TASKNAME] LIKE 'SO Approval%') ORDER BY CONVERT(VARCHAR(128), DATEADD(HOUR,2,dtf.[STARTEDDATETIME]),20),CONVERT(VARCHAR(128), DATEADD(HOUR,2,dtf.[COMPLETEDDATETIME]),20),[Duration in Days]
Take everything from the `FROM` clause down to the bottom (ignoring the order by) as this defines your target data set, and update that e.g.: UPDATE per SET per.[PERSONNAME] = 'New Name' FROM [Datamart].[dbo].[DM_REQUISITIONENTRYFACT] ref INNER JOIN [Datamart].[dbo].[DM_AUTHTASKFACT] dtf ON ref.[REQUISITIONID] = dtf.[REQUISITIONID] INNER JOIN [Datamart].[dbo].[V_DM_SERVICE] ser ON ref.[SERVICEID] = ser.[SERVICEID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] ini ON ref.[REQUESTORID] = ini.[PERSONID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] cus ON ref.[CUSTOMERID] = cus.[PERSONID] INNER JOIN [Datamart].[dbo].[V_DM_PERSON] per ON dtf.[PERFORMERID] = per.[PERSONID] WHERE dtf.[STARTEDDATETIME] BETWEEN '2016-08-01' AND '2016-09-21' AND ser.[SERVICENAME] = 'Lync' AND ref.[SERVICEREQUESTSTATUS] = 'Completed' AND (dtf.[TASKNAME] LIKE 'Service Owner Approval%' OR dtf.[TASKNAME] LIKE 'SO Approval%') I'd suggest running this in a `BEGIN TRAN....ROLLBACK` and also run the original query to see if it gives you the desired result, both in terms of data and expected number of rows touched - and once it does run with `COMMIT`
Hello Kev Thank You so much for your well explained response, really appreciate. Helped me gain a little understanding. I have run your query provided and left with the error message: "Update or insert of view or function 'per' failed because it contains a derived or constant field.". With reading now, I see I might need to add dummy data as I am entering a single insert on multi-table views. If this is the case, how could I work around this please?
Sorry hadn't realised that those are views - you need to restate the query using the base tables (for PER at least), but it feels like the query is to complex for what you need - after all if you update the name for the person that this query returns, and assuming a normalized data design, then that 'person' is updated regardless of the dtf.STARTDATETTIME and ser.SERVICENAME and ref.SERVICEREQUESTSTATUS etc. Is this a one-off update? Maybe better to retrieve the PK of the person and run a simple update from that manually?
instead of updating per, which is a view you need to update the underlying table - whatever that is!
17 People are following this question.
