如何从SQL Server中的SELECT进行更新？

在SQL Server中 ，可以使用SELECT语句insert到表中：

INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3 
FROM other_table 
WHERE sql = 'cool'

是否也可以通过SELECT进行更新？ 我有一个包含这些值的临时表，并希望使用这些值更新另一个表。 也许是这样的：

UPDATE Table SET col1, col2
SELECT col1, col2 
FROM other_table 
WHERE sql = 'cool'
WHERE Table.id = other_table.id

---

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

---

在SQL Server 2008（或更好）中，使用MERGE

MERGE INTO YourTable T
   USING other_table S 
      ON T.id = S.id
         AND S.tsql = 'cool'
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

或者：

MERGE INTO YourTable T
   USING (
          SELECT id, col1, col2 
            FROM other_table 
           WHERE tsql = 'cool'
         ) S
      ON T.id = S.id
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

---

UPDATE table 
SET Col1 = i.Col1, 
    Col2 = i.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) i
WHERE 
    i.ID = table.ID

链接地址: http://www.djcxy.com/p/335.html

上一篇: How do I UPDATE from a SELECT in SQL Server?

下一篇: How to replace all occurrences of a string in JavaScript?