Как я могу ОБНОВИТЬ из SELECT в SQL Server?

В 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
 
вопрос задан 25.02.2010
jamesmhaley
16582 репутация

28 ответов


  • 4555 рейтинг
    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'
    
    ответ дан Robin Day, с репутацией 77518, 25.02.2010
  • 685 рейтинг

    В 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;
     
    ответ дан onedaywhen, с репутацией 42324, 9.09.2011
  • 534 рейтинг
    UPDATE table 
    SET Col1 = i.Col1, 
        Col2 = i.Col2 
    FROM (
        SELECT ID, Col1, Col2 
        FROM other_table) i
    WHERE 
        i.ID = table.ID
    
    ответ дан Jamal, с репутацией 5357, 22.01.2012
  • 255 рейтинг

    Я бы изменил отличный ответ Робин на следующее:

     UPDATE Table
    SET Table.col1 = other_table.col1,
     Table.col2 = other_table.col2
    FROM
        Table
    INNER JOIN other_table ON Table.id = other_table.id
    WHERE
        Table.col1 != other_table.col1
    OR Table.col2 != other_table.col2
    OR (
        other_table.col1 IS NOT NULL
        AND Table.col1 IS NULL
    )
    OR (
        other_table.col2 IS NOT NULL
        AND Table.col2 IS NULL
    )
     

    Без предложения WHERE вы будете влиять на четные строки, которые не должны быть затронуты, что может (возможно) вызвать перерасчет индекса или триггеры запуска, которые действительно не должны были быть запущены.

    ответ дан quillbreaker, с репутацией 4715, 8.09.2011
  • 184 рейтинг

    В одну сторону

     UPDATE t 
    SET t.col1 = o.col1, 
        t.col2 = o.col2
    FROM 
        other_table o 
      JOIN 
        t ON t.id = o.id
    WHERE 
        o.sql = 'cool'
     
    ответ дан SQLMenace, с репутацией 108445, 25.02.2010
  • 143 рейтинг

    Еще одна возможность, о которой еще не упоминалось, состоит в том, чтобы просто вытолкнуть оператор SELECT в CTE, а затем обновить CTE.

     ;WITH CTE
         AS (SELECT T1.Col1,
                    T2.Col1 AS _Col1,
                    T1.Col2,
                    T2.Col2 AS _Col2
             FROM   T1
                    JOIN T2
                      ON T1.id = T2.id
             /*Where clause added to exclude rows that are the same in both tables
               Handles NULL values correctly*/
             WHERE EXISTS(SELECT T1.Col1,
                                 T1.Col2
                           EXCEPT
                           SELECT T2.Col1,
                                  T2.Col2))
    UPDATE CTE
    SET    Col1 = _Col1,
           Col2 = _Col2
     

    Это имеет то преимущество , что он легко запустить SELECT заявление на своих первых здравомыслие проверить результаты, но это не требует от вас псевдоним столбцов , как указано выше , если они одинаковые имена в таблицах исходных и целевых.

    Это также имеет те же ограничения, что и проприетарный синтаксис UPDATE ... FROM показанный в четырех других ответах. Если исходная таблица находится на многих сторонах соединения «один-ко-многим», то не определено, какая из возможных совпадающих объединенных записей будет использоваться в Update (проблема, которую MERGE избегает, поднимая ошибку, если есть попытка обновлять одну и ту же строку более одного раза).

    ответ дан Martin Smith, с репутацией 333672, 6.11.2011
  • 95 рейтинг

    Для записи (и других, как я был), вы можете сделать это в MySQL следующим образом:

     UPDATE first_table, second_table
    SET first_table.color = second_table.color
    WHERE first_table.id = second_table.foreign_id
     
    ответ дан Adrian Macneil, с репутацией 10176, 5.10.2012
  • 81 рейтинг

    Использование псевдонима:

     UPDATE t
       SET t.col1 = o.col1
      FROM table1 AS t
             INNER JOIN 
           table2 AS o 
             ON t.id = o.id
     
    ответ дан rageit, с репутацией 2724, 23.05.2012
  • 62 рейтинг

    Простой способ сделать это:

     UPDATE
        table_to_update,
        table_info
    SET
        table_to_update.col1 = table_info.col1,
        table_to_update.col2 = table_info.col2
    
    WHERE
        table_to_update.ID = table_info.ID
     
    ответ дан Patrick Frenette, с репутацией 661, 14.11.2012
  • 49 рейтинг

    Это может быть нишей причиной для выполнения обновления (например, в основном используется в процедуре) или может быть очевидным для других, но также следует указать, что вы можете выполнить инструкцию выбора выбора без использования соединения (в случае, если таблицы, которые вы обновляете, не имеют общего поля).

     update
        Table
    set
        Table.example = a.value
    from
        TableExample a
    where
        Table.field = *key value* -- finds the row in Table 
        AND a.field = *key value* -- finds the row in TableExample a
     
    ответ дан Ryan, с репутацией 1518, 11.06.2012
  • 47 рейтинг

    Вот еще один полезный синтаксис:

     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».

    ответ дан efirat, с репутацией 2149, 2.05.2013
  • 45 рейтинг

    Если вы используете MySQL вместо SQL Server, синтаксис следующий:

     UPDATE Table1
    INNER JOIN Table2
    ON Table1.id = Table2.id
    SET Table1.col1 = Table2.col1,
        Table1.col2 = Table2.col2
     
    ответ дан Hentold, с репутацией 633, 3.10.2013
  • 45 рейтинг

    Я добавляю это только для того, чтобы вы могли быстро просмотреть его, чтобы вы могли проверить, что будет обновляться до выполнения обновления.

     UPDATE Table 
    SET  Table.col1 = other_table.col1,
         Table.col2 = other_table.col2 
    --select Table.col1, other_table.col,Table.col2,other_table.col2, *   
    FROM     Table 
    INNER JOIN     other_table 
        ON     Table.id = other_table.id 
     
    ответ дан HLGEM, с репутацией 78315, 8.09.2011
  • 39 рейтинг

    UPDATE из SELECT с INNER JOIN в базе данных SQL

    Поскольку слишком много ответов на этот пост, которые наиболее сильно опротестованы, я подумал, что также предлагаю свое предложение. Хотя вопрос очень интересный, я видел на многих сайтах форума и сделал решение, используя INNER JOIN со скриншотами.

    Сначала я создал таблицу с именем schoolold и вставил несколько записей в отношении их имен столбцов и выполнил их.

    Затем я выполнил команду SELECT для просмотра вставленных записей.

    введите описание изображения здесь

    Затем я создал новую таблицу с именем schoolnew и аналогично выполнил над ней действия.

    введите описание изображения здесь

    Затем, чтобы просмотреть вставленные записи в нем, я выполняю команду SELECT.

    введите описание изображения здесь

    Теперь, здесь я хочу внести некоторые изменения в третью и четвертую строки, чтобы завершить это действие, я выполняю команду UPDATE с INNER JOIN .

    введите описание изображения здесь

    Чтобы просмотреть изменения, я выполняю команду SELECT .

    введите описание изображения здесь

    Вы можете видеть, как третья и четвертая записи школьного школьного стола легко заменяются таблицей schoolnew , используя INNER JOIN с инструкцией UPDATE.

    ответ дан Jason Clark, с репутацией 1194, 30.11.2015
  • 35 рейтинг

    И если вы хотели бы присоединиться к таблице с собой (что не произойдет слишком часто):

     update t1                    -- just reference table alias here
    set t1.somevalue = t2.somevalue
    from table1 t1               -- these rows will be the targets
    inner join table1 t2         -- these rows will be used as source
    on ..................        -- the join clause is whatever suits you
     
    ответ дан Jakub, с репутацией 1009, 27.06.2014
  • 33 рейтинг

    В следующем примере используется производная таблица, инструкция SELECT после предложения FROM, для возврата старых и новых значений для последующих обновлений:

     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
     
    ответ дан Aleksandr Fedorenko, с репутацией 12444, 25.09.2013
  • 30 рейтинг

    Обновление через 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
     
    ответ дан Pரதீப், с репутацией 74063, 12.12.2015
  • 30 рейтинг

    Если вы используете SQL Server, вы можете обновить одну таблицу от другой без указания соединения и просто связать два из предложения where . Это делает гораздо более простой SQL-запрос:

     UPDATE Table1
    SET Table1.col1 = Table2.col1,
        Table1.col2 = Table2.col2
    FROM
        Table2
    WHERE
        Table1.id = Table2.id
     
    ответ дан Richard, с репутацией 879, 20.02.2017
  • 19 рейтинг
     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
     
    ответ дан Yaman, с репутацией 650, 30.11.2016
  • 19 рейтинг

    Другой способ - использовать производную таблицу:

     UPDATE t
    SET t.col1 = a.col1
        ,t.col2 = a.col2
    FROM (
    SELECT id, col1, col2 FROM @tbl2) a
    INNER JOIN @tbl1 t ON t.id = a.id
     

    Пример данных

     DECLARE @tbl1 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10))
    DECLARE @tbl2 TABLE (id INT, col1 VARCHAR(10), col2 VARCHAR(10))
    
    INSERT @tbl1 SELECT 1, 'a', 'b' UNION SELECT 2, 'b', 'c'
    
    INSERT @tbl2 SELECT 1, '1', '2' UNION SELECT 2, '3', '4'
    
    UPDATE t
    SET t.col1 = a.col1
        ,t.col2 = a.col2
    FROM (
    SELECT id, col1, col2 FROM @tbl2) a
    INNER JOIN @tbl1 t ON t.id = a.id
    
    SELECT * FROM @tbl1
    SELECT * FROM @tbl2
     
    ответ дан sqluser, с репутацией 4240, 24.02.2016
  • 16 рейтинг

    Использование:

     drop table uno
    drop table dos
    
    create table uno
    (
        uid int,
        col1 char(1),
        col2 char(2)
    )
    create table dos
    (
        did int,
        col1 char(1),
        col2 char(2),
        [sql] char(4)
    )
    insert into uno(uid) values (1)
    insert into uno(uid) values (2)
    insert into dos values (1,'a','b',null)
    insert into dos values (2,'c','d','cool')
    
    select * from uno 
    select * from dos
     

    ИЛИ:

     update uno set col1 = (select col1 from dos where uid = did and [sql]='cool'), 
    col2 = (select col2 from dos where uid = did and [sql]='cool')
     

    ИЛИ:

     update uno set col1=d.col1,col2=d.col2 from uno 
    inner join dos d on uid=did where [sql]='cool'
    
    select * from uno 
    select * from dos
     

    Если имя столбца ID одинаково в обеих таблицах, просто поместите имя таблицы перед обновлением таблицы и используйте псевдоним для выбранной таблицы, то есть:

     update uno set col1 = (select col1 from dos d where uno.[id] = d.[id] and [sql]='cool'),
    col2  = (select col2 from dos d where uno.[id] = d.[id] and [sql]='cool')
     
    ответ дан russ, с репутацией 413, 18.06.2014
  • 14 рейтинг

    Ниже приведенное решение работает для базы данных MySQL:

     UPDATE table1 a , table2 b 
    SET a.columname = 'some value' 
    WHERE b.columnname IS NULL ;
     
    ответ дан Mateen, с репутацией 658, 1.10.2014
  • 14 рейтинг

    Существует даже более короткий метод, и это может удивить многих из вас:

     -- Sample data:
    ---------------------------------------------------------------------------
    CREATE TABLE #SOURCE ([ID] INT, [Desc] VARCHAR(10));
    CREATE TABLE #DESTINATION ([ID] INT, [Desc] VARCHAR(10))
    
    INSERT INTO #SOURCE VALUES(1,'Desc_1'), (2, 'Desc_2'), (3, 'Desc_3');
    INSERT INTO #DESTINATION VALUES(1,'Desc_4'), (2, 'Desc_5'), (3, 'Desc_6');
    ---------------------------------------------------------------------------
    UPDATE #DESTINATION
    SET #DESTINATION.[Desc] = #SOURCE.[Desc]
    FROM #SOURCE
    WHERE #DESTINATION.[ID] = #SOURCE.[ID]
    AND #Source.[Desc] = 'Desc_2'
     
    ответ дан Bartosz X, с репутацией 782, 26.01.2017
  • 13 рейтинг

    Другой способ обновления из оператора 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'
     
    ответ дан Govind Tupkar, с репутацией 258, 8.09.2016
  • 13 рейтинг

    В принятом ответе после:

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

    Я бы добавил:

     OUTPUT deleted.*, inserted.*
     

    То, что я обычно делаю, это положить все в транзакцию с резервными копиями и использовать "OUTPUT" : таким образом я вижу все, что должно произойти. Когда я доволен тем, что вижу, я меняю ROLLBACK на COMMIT .

    Мне обычно нужно документировать, что я сделал, поэтому я использую параметр "results to Text" когда запускаю запрос с поддержкой roll-backed, и сохраняю как скрипт, так и результат OUTPUT. (Конечно, это непрактично, если я изменил слишком много строк)

    ответ дан Johannes Wentu, с репутацией 478, 27.04.2017
  • 11 рейтинг
    UPDATE table AS a
    INNER JOIN table2 AS b
    ON a.col1 = b.col1
    INNER JOIN ... AS ...
    ON ... = ...
    SET ...
    WHERE ...
    
    ответ дан Cornezuelo del Centeno, с репутацией 370, 31.07.2015
  • 5 рейтинг

    Здесь объединены все различные подходы.

    1. Выберите обновление
    2. Обновление с помощью общего выражения таблицы
    3. сливаться

    Пример структуры таблицы ниже и будет обновляться из 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]
     

    Product_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);
     
    ответ дан Abdul Azeez, с репутацией 158, 31.01.2018
  • 0 рейтинг

    Вы можете использовать это для обновления на сервере sql

     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'
     
    ответ дан Erfan Mohammadi, с репутацией 102, 31.08.2018