在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

当前回答

通过CTE进行更新比此处的其他答案更具可读性:

;WITH cte
     AS (SELECT col1,col2,id
         FROM   other_table
         WHERE  sql = 'cool')
UPDATE A
SET    A.col1 = B.col1,
       A.col2 = B.col2
FROM   table A
       INNER JOIN cte B
               ON A.id = B.id

其他回答

如果您使用的是SQL Server,则可以在不指定联接的情况下从另一个表更新一个表,并且只需从where子句链接这两个表。这使得SQL查询更加简单:

UPDATE Table1
SET Table1.col1 = Table2.col1,
    Table1.col2 = Table2.col2
FROM
    Table2
WHERE
    Table1.id = Table2.id

像其他人一样,必须指出MySQL或MariaDB使用不同的语法。它还支持非常方便的USING语法(与T/SQL相反)。INNER JOIN也是JOIN的同义词。因此,原始问题中的查询最好在MySQL中实现:

UPDATE
    Some_Table AS Table_A

JOIN
    Other_Table AS Table_B USING(id)

SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2

WHERE
    Table_A.col3 = 'cool'

我在其他答案中没有看到问题的答案,因此我的两分钱。(在PHP 7.4.0 MariaDB 10.4.10上测试)

甚至还有一种更短的方法,可能会让你感到惊讶:

示例数据集:

CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10));
CREATE TABLE #DEST   ([ID] INT, [Desc] VARCHAR(10));

INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3');
INSERT INTO #DEST   VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6');

代码:

UPDATE #DEST
SET #DEST.[Desc] = #SOURCE.[Desc]
FROM #SOURCE
WHERE #DEST.[ID] = #SOURCE.[ID];

选项1:使用内部联接:

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

选项2:共同相关子查询

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

我以前使用过INSERT SELECT。对于那些想使用新东西的人来说,这里有一个类似的解决方案,但它要短得多:

UPDATE table1                                          // Table that's going to be updated.
LEFT JOIN                                              // Type of join.
    table2 AS tb2                                      // Second table and rename for easy.
ON
    tb2.filedToMatchTables = table1.fieldToMatchTables // Fields to connect both tables.
SET
    fieldFromTable1 = tb2.fieldFromTable2;             // Field to be updated on table1.

    field1FromTable1 = tb2.field1FromTable2,           // This is in the case you need to
    field1FromTable1 = tb2.field1FromTable2,           // update more than one field.
    field1FromTable1 = tb2.field1FromTable2;           // Remember to put ; at the end.