在SQL Server中,可以使用insert将行插入到表中。。SELECT语句:

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 t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

其他回答

同样的解决方案可以用稍微不同的方式编写,因为我只想设置一次列。这两张桌子我都写过了。它在MySQL中工作。

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

以下示例使用派生表(FROM子句后的SELECT语句)返回旧值和新值以供进一步更新:

UPDATE x
SET    x.col1 = x.newCol1,
       x.col2 = x.newCol2
FROM   (SELECT t.col1,
               t2.col1 AS newCol1,
               t.col2,
               t2.col2 AS newCol2
        FROM   [table] t
               JOIN other_table t2
                 ON t.ID = t2.ID) x
UPDATE YourTable 
SET Col1 = OtherTable.Col1, 
    Col2 = OtherTable.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) AS OtherTable
WHERE 
    OtherTable.ID = YourTable.ID

Oracle SQL(使用别名):

UPDATE Table T 
SET T.col1 = (SELECT OT.col1 WHERE OT.id = T.id),
T.col2 = (SELECT OT.col2 WHERE OT.id = T.id);

使用别名:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id