SQL Server数据库如何将一个表的某个/某些字段的数据更新成另一个表的某个/某些字段的数据呢？

6 个回答

• 0

  使用INNER JOIN连接A,B两张表可以实现一条SQK脚本语句将一个表的字段数据更新成另一个表相应的字段值，如下：

  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'
  

  评论 分享

  

  Rector

  2020-04-28 回答
• 0

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

  评论 分享

  

  Rector

  2020-04-28 回答
• 0

  用一个子查询来实现关联更新，SQL脚本如下：

  UPDATE YourTable
  SET Col1 = OtherTable.Col1,
      Col2 = OtherTable.Col2
  FROM (
      SELECT ID, Col1, Col2
      FROM other_table) AS OtherTable
  WHERE
      OtherTable.ID = YourTable.ID
  

  评论 分享

  

  Rector

  2020-04-28 回答
• 0

  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'
  

  评论 分享

  

  Rector

  2020-04-28 回答
• 0

  使用CTE来实现SQL的关联更新，如下：

  ;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 EXISTS(SELECT T1.Col1,
                               T1.Col2
                         EXCEPT
                         SELECT T2.Col1,
                                T2.Col2))
  UPDATE CTE
  SET    Col1 = _Col1,
         Col2 = _Col2
  

  评论 分享

  

  Rector

  2020-04-28 回答
• 0

  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
  

  评论 分享

  

  Rector

  2020-04-28 回答