SQL 2008 R2 - Update a table using VIEW?

• graham 47698

  SSC Journeyman

  Points: 99

  More actions

  January 12, 2014 at 12:53 am

  #284438

  I have the below VIEW that I now want to use the selected columns to UPDATE another table with the same 'ProjectId'..

  Can anyone assist me please ?

  SELECT project.id AS projectId, task.id AS taskId, [task-assignment].[hourly-rate] * [task-assignment].budget AS BudgetValue,

  [task-assignment].[hourly-rate] * SUM(dayentry.hours) AS ActualValue, SUM(dayentry.hours) AS ActualHours, [task-assignment].budget AS BudgetHours

  FROM dbo.[task-assignment] AS [task-assignment] INNER JOIN

  dbo.project AS project ON [task-assignment].[project-id] = project.id INNER JOIN

  dbo.task AS task ON [task-assignment].[task-id] = task.id FULL OUTER JOIN

  dbo.dayentry AS dayentry ON project.id = dayentry.[project-id] AND task.id = dayentry.[task-id]

  WHERE (project.[bill-by] = 'Tasks') AND ([task-assignment].billable = 1)

  GROUP BY project.id, task.id, [task-assignment].[hourly-rate], [task-assignment].budget

• Damian Widera-396333

  Ten Centuries

  Points: 1186

  More actions

  January 12, 2014 at 2:53 am

  #1679406

  Try to do it that way:

  UPDATE T

  Set t.column_in_table = V.column_in_view

  FROM Table T join View V

  on T.a_table_column = V.a_view_column

  WHERE <conditions>

  Kindest Regards,

  Damian Widera
  SQL Server MVP,
  MCT, MCSE Data Platform, MCSD.NET