Обновить таблицу с помощью JOIN в SQL Server?
Я хочу обновить столбец в таблице, создавая соединение с другой таблицей, например:
UPDATE table1 a
INNER JOIN table2 b ON a.commonfield = b.[common field]
SET a.CalculatedColumn= b.[Calculated Column]
WHERE
b.[common field]= a.commonfield
AND a.BatchNO = '110'
Но это жалуется
Msg 170, уровень 15, состояние 1, строка 2
Строка 2: неправильный синтаксис рядом с 'a'.
Что здесь не так?
13 ответов
У вас не совсем проприетарный SQL Server UPDATE FROM Синтаксис вниз. Также не уверен, почему вам нужно было присоединиться к CommonField а также отфильтровать его позже. Попробуй это:
UPDATE t1
SET t1.CalculatedColumn = t2.[Calculated Column]
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.CommonField = t2.[Common Field]
WHERE t1.BatchNo = '110';
Если вы делаете что-то действительно глупое - например, постоянно пытаетесь установить значение одного столбца в совокупность другого столбца (что нарушает принцип избегания хранения избыточных данных), вы можете использовать CTE (общее табличное выражение) - см. Здесь а здесь для более подробной информации:
;WITH t2 AS
(
SELECT [key], CalculatedColumn = SUM(some_column)
FROM dbo.table2
GROUP BY [key]
)
UPDATE t1
SET t1.CalculatedColumn = t2.CalculatedColumn
FROM dbo.table1 AS t1
INNER JOIN t2
ON t1.[key] = t2.[key];
Причина, по которой это действительно глупо, заключается в том, что вам придется перезапускать все это обновление каждый раз в любой строке table2 изменения. SUM это то, что вы всегда можете вычислить во время выполнения, и при этом никогда не нужно беспокоиться о том, что результат устарел.
Попробуйте это так:
begin tran
UPDATE a
SET a.CalculatedColumn= b.[Calculated Column]
FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field]
WHERE a.BatchNO = '110'
commit tran
Ответ, данный выше Аароном, совершенен:
UPDATE a
SET a.CalculatedColumn = b.[Calculated Column]
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.CommonField = b.[Common Field]
WHERE a.BatchNo = '110';
Просто хочу добавить, почему эта проблема возникает в SQL Server, когда мы пытаемся использовать псевдоним таблицы при обновлении этой таблицы, приведенный ниже синтаксис всегда будет выдавать ошибку:
update tableName t
set t.name = 'books new'
where t.id = 1
регистр может быть любым, если вы обновляете одну таблицу или обновляете при использовании объединения.
Хотя приведенный выше запрос будет хорошо работать в PL / SQL, но не в SQL Server.
Правильный способ обновления таблицы при использовании псевдонима таблицы в SQL Server:
update t
set t.name = 'books new'
from tableName t
where t.id = 1
Надеюсь, это поможет всем, почему ошибка пришла сюда.
MERGE table1 T
USING table2 S
ON T.CommonField = S."Common Field"
AND T.BatchNo = '110'
WHEN MATCHED THEN
UPDATE
SET CalculatedColumn = S."Calculated Column";
UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
From mytable
Join otherTable on otherTable.id = mytable.id
Where othertable.somecolumn = '1234'
Другие варианты здесь: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/
Похоже, что SQL Server 2012 может обрабатывать старый синтаксис обновления Teradata:
UPDATE a
SET a.CalculatedColumn= b.[Calculated Column]
FROM table1 a, table2 b
WHERE
b.[common field]= a.commonfield
AND a.BatchNO = '110'
Если я правильно помню, 2008R2 выдавал ошибку, когда я пытался подобный запрос.
Я считаю полезным превратить UPDATE в SELECT, чтобы получить строки, которые я хочу обновить в качестве теста перед обновлением. Если я могу выбрать точные строки, которые я хочу, я могу обновить только те строки, которые я хочу обновить.
DECLARE @expense_report_id AS INT
SET @expense_report_id = 1027
--UPDATE expense_report_detail_distribution
--SET service_bill_id = 9
SELECT *
FROM expense_report_detail_distribution erdd
INNER JOIN expense_report_detail erd
INNER JOIN expense_report er
ON er.expense_report_id = erd.expense_report_id
ON erdd.expense_report_detail_id = erd.expense_report_detail_id
WHERE er.expense_report_id = @expense_report_id
Другой подход будет использовать MERGE
;WITH cteTable1(CalculatedColumn, CommonField)
AS
(
select CalculatedColumn, CommonField from Table1 Where BatchNo = '110'
)
MERGE cteTable1 AS target
USING (select "Calculated Column", "Common Field" FROM dbo.Table2) AS source ("Calculated Column", "Common Field")
ON (target.CommonField = source."Common Field")
WHEN MATCHED THEN
UPDATE SET target.CalculatedColumn = source."Calculated Column";
-Merge является частью стандарта SQL
-Также я почти уверен, что обновления внутреннего соединения не являются детерминированными. Подобный вопрос здесь, где ответ говорит об этом http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query.html
Думаю, это то, что вы ищете.
UPDATE
Table1
SET
Table1.columeName =T1.columeName * T2.columeName
FROM
Table1 T1
INNER JOIN Table2 T2
ON T1.columeName = T2.columeName;
Приведенный выше подход Аарона отлично сработал для меня. Мой оператор обновления немного отличался, потому что мне нужно было объединиться на основе двух полей, объединенных в одну таблицу, чтобы соответствовать полю в другой таблице.
--update clients table cell field from custom table containing mobile numbers
update clients
set cell = m.Phone
from clients as c
inner join [dbo].[COSStaffMobileNumbers] as m
on c.Last_Name + c.First_Name = m.Name
У меня была та же проблема.. и вам не нужно добавлять физический столбец.. потому что теперь вам придется его поддерживать... что вы можете сделать, это добавить общий столбец в запросе выбора:
EX:
select tb1.col1, tb1.col2, tb1.col3 ,
(
select 'Match' from table2 as tbl2
where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2
)
from myTable as tbl1
Те, кто использует MYSQL
UPDATE table1 INNER JOIN table2 ON table2.id = table1.id SET table1.status = 0 WHERE table1.column = 20
Пытаться:
UPDATE table1
SET CalculatedColumn = ( SELECT [Calculated Column]
FROM table2
WHERE table1.commonfield = [common field])
WHERE BatchNO = '110'