在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

当前回答

整合所有不同的方法。

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

示例表结构如下,将从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中进行更新:

UPDATE
    T1
SET
   T1.col1 = T2.col1,
   T1.col2 = T2.col2
FROM
   Table1 AS T1
INNER JOIN Table2 AS T2
    ON T1.id = T2.id
WHERE
    T1.col3 = 'cool'
UPDATE table AS a
INNER JOIN table2 AS b
ON a.col1 = b.col1
INNER JOIN ... AS ...
ON ... = ...
SET ...
WHERE ...

整合所有不同的方法。

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

示例表结构如下,将从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);
UPDATE TQ
SET TQ.IsProcessed = 1, TQ.TextName = 'bla bla bla'
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

要确保正在更新所需内容,请先选择

SELECT TQ.IsProcessed, 1 AS NewValue1, TQ.TextName, 'bla bla bla' AS NewValue2
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

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

示例数据集:

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