在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 suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

它使用“WHERE EXIST”检查它是否为空。

其他回答

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

示例数据集:

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];

像其他人一样,必须指出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上测试)

整合所有不同的方法。

选择更新使用公共表表达式更新合并

示例表结构如下,将从Product_BAK更新为Product表。

表产品

CREATE TABLE [dbo].[Product](
    [Id] [int] IDENTITY(1, 1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Description] [nvarchar](100) NULL
) ON [PRIMARY]

表产品_BAK

    CREATE TABLE [dbo].[Product_BAK](
        [Id] [int] IDENTITY(1, 1) NOT NULL,
        [Name] [nvarchar](100) NOT NULL,
        [Description] [nvarchar](100) NULL
    ) ON [PRIMARY]

1.选择更新

    update P1
    set Name = P2.Name
    from Product P1
    inner join Product_Bak P2 on p1.id = P2.id
    where p1.id = 2

2.使用公共表表达式更新

    ; With CTE as
    (
        select id, name from Product_Bak where id = 2
    )
    update P
    set Name = P2.name
    from  product P  inner join CTE P2 on P.id = P2.id
    where P2.id = 2

3.合并

    Merge into product P1
    using Product_Bak P2 on P1.id = P2.id

    when matched then
    update set p1.[description] = p2.[description], p1.name = P2.Name;

在这个Merge语句中,如果在目标中找不到匹配的记录,但在源中存在,我们可以插入,请查找语法:

    Merge into product P1
    using Product_Bak P2 on P1.id = P2.id;

    when matched then
    update set p1.[description] = p2.[description], p1.name = P2.Name;

    WHEN NOT MATCHED THEN
    insert (name, description)
    values(p2.name, P2.description);

在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;

从select语句更新的另一种方法:

UPDATE A
SET A.col = A.col,B.col1 = B.col1
FROM  first_Table AS A
INNER JOIN second_Table AS B  ON A.id = B.id WHERE A.col2 = 'cool'