Обновить таблицу с помощью JOIN в SQL Server?
Я бы посмотрел на плагин, такой как yepnopejs
yepnope([{
load: 'http://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js',
complete: function () {
if (!window.jQuery) {
yepnope('local/jquery.min.js');
}
}
}]);
Получает массив объекта для проверки, проверяет документацию на сайте
4 ответа
You don't quite have SQL Server's proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this:
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';
If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:
;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];
The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in table2 changes. A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.
Подход Aaron выше обработанного отлично для меня. Мой оператор обновления немного отличался, потому что я должен был присоединиться на основе двух полей, связанных в одной таблице для соответствия полю в другой таблице.
--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
Попробуйте:
UPDATE table1
SET CalculatedColumn = ( SELECT [Calculated Column]
FROM table2
WHERE table1.commonfield = [common field])
WHERE BatchNO = '110'
Попробуйте это так:
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