SQL query (UPDATE)?

Question

SQL query optimization

Problem with writing UPDATE request.
There are two tables: Counterparties and Tasks
of counterparties There is a query like:

SELECT account.Name,activity.Id,account.ModifiedOn 
FROM  Account account LEFT JOIN Activity activity ON activity.AccountId=account.Id  
WHERE account.TypeId='03a75490-53e6-df11-971b-001d60e938c6'  
  AND activity.Id IS NULL AND DATEDIFF(dd,account.ModifiedOn,GETDATE())>60

This query displays a list of accounts for which the last modified date is more than 60 days and there were no tasks.
The question is how to modify this data.

UPDATE Account 
SET UsrLastActivityDate=GETDATE(), UsrProsrochen=1 
WHERE TypeId='03a75490-53e6-df11-971b-001d60e938c6' 
 AND DATEDIFF(dd,ModifiedOn,GETDATE())>60 и (?????не было задач)

Answer 1

The syntax of the T-SQL UPDATE command allows you to update a single table, but you can use multiple tables to filter like a SELECT command.
Those. In general it looks like this

UPDATE
  ОбновляемаяТаблица
SET
  ПолеОбновляемойТаблицы1 = Значение1
  , ПолеОбновляемойТаблицы2 = Значение2
FROM
  ОбновляемаяТаблица
LEFT OUTER JOIN
  ДополнительнаяТаблица
ON
  ДополнительнаяТаблица.Ключ = ОбновляемаяТаблица.Ключ
WHERE
  Условия фильтрации

For the conditions of your task, you can use this template to convert the conditions from your SELECT query into conditions for UPDATE:

UPDATE
  Account
SET
  UsrLastActivityDate = GETDATE()
  , UsrProsrochen = 1
FROM
  Account account -- Основная обновляемая таблица
LEFT OUTER JOIN
 Activity activity -- дополнительная таблица с информацией о задачах
ON
 activity.AccountId = account.Id
WHERE 
 TypeId = '03a75490-53e6-df11-971b-001d60e938c6'
  AND DATEDIFF(dd, ModifiedOn ,GETDATE()) > 60
  AND activity.Id IS NULL -- аналогичный SELECT фильтр на отсутствие задач