使用连接的SQL更新查询

9 回答

• 57

  MySQL: In general, make necessary changes par your requirement:
  
  UPDATE
      shopping_cart sc
      LEFT JOIN
      package pc ON sc. package_id = pc.id    
  SET
      sc. amount = pc.amount
  

  回复于 2024-05-09T10:25:40+08:00
• 1058

  试试这样......

  Update t1.Column1 = value 
  from tbltemp as t1 
  inner join tblUser as t2 on t2.ID = t1.UserID 
  where t1.[column1]=value and t2.[Column1] = value;
  

  回复于 2024-05-09T10:25:40+08:00
• 9

  没有使用上面的sql，但这里是一个基于连接语句更新表的示例 .

  UPDATE p
  SET    p.category = c.category
  FROM   products p
         INNER JOIN prodductcatagories pg
              ON  p.productid = pg.productid
         INNER JOIN categories c
              ON  pg.categoryid = c.cateogryid
  WHERE  c.categories LIKE 'whole%'
  

  回复于 2024-05-09T10:25:40+08:00
• 62

  UPDATE im
  SET mf_item_number = gm.SKU --etc
  FROM item_master im
  JOIN group_master gm
      ON im.sku = gm.sku 
  JOIN Manufacturer_Master mm
      ON gm.ManufacturerID = mm.ManufacturerID
  WHERE im.mf_item_number like 'STA%' AND
        gm.manufacturerID = 34
  

  为了清楚起见...... UPDATE 子句可以引用 FROM 子句中指定的表别名 . 所以 im 在这种情况下是有效的

  通用示例

  UPDATE A
  SET foo = B.bar
  FROM TableA A
  JOIN TableB B
      ON A.col1 = B.colx
  WHERE ...
  

  回复于 2024-05-09T10:25:40+08:00
• 7

  将其改编为MySQL - UPDATE 中没有 FROM 子句，但这有效：

  UPDATE
      item_master im
      JOIN
      group_master gm ON im.sku=gm.sku 
      JOIN
      Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerID
  SET
      im.mf_item_number = gm.SKU --etc
  WHERE
      im.mf_item_number like 'STA%'
      AND
      gm.manufacturerID=34
  

  回复于 2024-05-09T10:25:40+08:00
• 4

  您可以使用MERGE Command更新对 MATCHED 和 NOT MATCHED 的更多控制:(我略微更改了源代码以演示我的观点）

  USE tempdb;
  GO
  IF(OBJECT_ID('target') > 0)DROP TABLE dbo.target
  IF(OBJECT_ID('source') > 0)DROP TABLE dbo.source
  CREATE TABLE dbo.Target
      (
        EmployeeID INT ,
        EmployeeName VARCHAR(100) ,
        CONSTRAINT Target_PK PRIMARY KEY ( EmployeeID )
      );
  CREATE TABLE dbo.Source
      (
        EmployeeID INT ,
        EmployeeName VARCHAR(100) ,
        CONSTRAINT Source_PK PRIMARY KEY ( EmployeeID )
      );
  GO
  INSERT  dbo.Target
          ( EmployeeID, EmployeeName )
  VALUES  ( 100, 'Mary' );
  INSERT  dbo.Target
          ( EmployeeID, EmployeeName )
  VALUES  ( 101, 'Sara' );
  INSERT  dbo.Target
          ( EmployeeID, EmployeeName )
  VALUES  ( 102, 'Stefano' );
  
  GO
  INSERT  dbo.Source
          ( EmployeeID, EmployeeName )
  VALUES  ( 100, 'Bob' );
  INSERT  dbo.Source
          ( EmployeeID, EmployeeName )
  VALUES  ( 104, 'Steve' );
  GO
  
  SELECT * FROM dbo.Source
  SELECT * FROM dbo.Target
  
  MERGE Target AS T
  USING Source AS S
  ON ( T.EmployeeID = S.EmployeeID )
  WHEN MATCHED THEN
      UPDATE SET T.EmployeeName = S.EmployeeName + '[Updated]';
  GO 
  SELECT '-------After Merge----------'
  SELECT * FROM dbo.Source
  SELECT * FROM dbo.Target
  

  回复于 2024-05-09T10:25:40+08:00
• 0

  最简单的方法之一是使用公用表表达式（因为您已经在SQL 2005上）：

  with cte as (
  select
      im.itemid
      ,im.sku as iSku
      ,gm.SKU as GSKU
      ,mm.ManufacturerId as ManuId
      ,mm.ManufacturerName
      ,im.mf_item_number
      ,mm.ManufacturerID
      , <your other field>
  from 
      item_master im, group_master gm, Manufacturer_Master mm 
  where
      im.mf_item_number like 'STA%'
      and im.sku=gm.sku
      and gm.ManufacturerID = mm.ManufacturerID
      and gm.manufacturerID=34)
  update cte set mf_item_number = <your other field>
  

  查询执行引擎将自己弄清楚如何更新记录 .

  回复于 2024-05-09T10:25:40+08:00
• 2

  您可以使用以下查询：

  UPDATE im
  SET mf_item_number = (some value) 
  FROM item_master im
  JOIN group_master gm
      ON im.sku = gm.sku 
  JOIN Manufacturer_Master mm
      ON gm.ManufacturerID = mm.ManufacturerID
  WHERE im.mf_item_number like 'STA%' AND
        gm.manufacturerID = 34    `sql`
  

  回复于 2024-05-09T10:25:40+08:00
• 0

  您可以使用UPDATE语句中的“FROM”子句指定用于确定更新方式和更新内容的其他表，如下所示：

  update item_master
  set mf_item_number = (some value)
  from 
     group_master as gm
     join Manufacturar_Master as mm ON ........
  where
   .... (your conditions here)
  

  在WHERE子句中，您需要提供条件和连接操作以将这些表绑定在一起 .

  渣

  回复于 2024-05-09T10:25:40+08:00