Very Computer

Dear All,
How can I update multiple tables by using single update query.

This is not working... Please help

  UPDATE   tbl_Shp_Order_Dtl, tbl_Cmp_Product
  SET    tbl_Shp_Order_Dtl.Company_Codes=tbl_Cmp_Product.Company_Codes,
     tbl_Shp_Order_Dtl.W_Cost=tbl_Cmp_Product.W_Cost,
    tbl_Cmp_Product.Quantity_Sold=tbl_Cmp_Product.Quantity_Sold+1
  FROM  tbl_Cmp_Product
  INNER JOIN tbl_Shp_Order_Dtl
  ON  tbl_Cmp_Product.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
  AND  tbl_Cmp_Product.Characteristics = tbl_Shp_Order_Dtl.Characteristics
  WHERE  (tbl_Cmp_Product.Company_Codes = 16)
  AND  (tbl_Shp_Order_Dtl.Order_Dtl_Codes = 74)

Best Regards,

Muhammad T. Alam

Quote:> How can I update multiple tables by using single update query.

You can't each modification statement can only modify one table.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer

Quote:> Dear All,
> How can I update multiple tables by using single update query.

> This is not working... Please help

>   UPDATE   tbl_Shp_Order_Dtl, tbl_Cmp_Product
>   SET    tbl_Shp_Order_Dtl.Company_Codes=tbl_Cmp_Product.Company_Codes,
>      tbl_Shp_Order_Dtl.W_Cost=tbl_Cmp_Product.W_Cost,
>     tbl_Cmp_Product.Quantity_Sold=tbl_Cmp_Product.Quantity_Sold+1
>   FROM  tbl_Cmp_Product
>   INNER JOIN tbl_Shp_Order_Dtl
>   ON  tbl_Cmp_Product.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
>   AND  tbl_Cmp_Product.Characteristics = tbl_Shp_Order_Dtl.Characteristics
>   WHERE  (tbl_Cmp_Product.Company_Codes = 16)
>   AND  (tbl_Shp_Order_Dtl.Order_Dtl_Codes = 74)

> Best Regards,

> Muhammad T. Alam

An UPDATE refers to one table only.

Your query logic looks a little strange to me but I think this may be what
you were trying to do:

UPDATE tbl_Shp_Order_Dtl
 SET company_codes = 16,
     w_cost=
  (SELECT w_cost FROM tbl_cmp_product AS P
   WHERE P.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
     AND P.Characteristics = tbl_Shp_Order_Dtl.Characteristics
     AND P.Company_Codes = 16)
  WHERE EXISTS
   (SELECT * FROM tbl_cmp_product AS P
    WHERE P.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
      AND P.Characteristics = tbl_Shp_Order_Dtl.Characteristics
      AND P.Company_Codes = 16)
   AND Order_Dtl_Codes = 74

UPDATE tbl_Cmp_Product SET Quantity_Sold = Quantity_Sold + 1

--
David Portas
------------
Please reply only to the newsgroup
--

Quote:> Dear All,
> How can I update multiple tables by using single update query.

> This is not working... Please help

>   UPDATE   tbl_Shp_Order_Dtl, tbl_Cmp_Product
>   SET    tbl_Shp_Order_Dtl.Company_Codes=tbl_Cmp_Product.Company_Codes,
>      tbl_Shp_Order_Dtl.W_Cost=tbl_Cmp_Product.W_Cost,
>     tbl_Cmp_Product.Quantity_Sold=tbl_Cmp_Product.Quantity_Sold+1
>   FROM  tbl_Cmp_Product
>   INNER JOIN tbl_Shp_Order_Dtl
>   ON  tbl_Cmp_Product.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
>   AND  tbl_Cmp_Product.Characteristics = tbl_Shp_Order_Dtl.Characteristics
>   WHERE  (tbl_Cmp_Product.Company_Codes = 16)
>   AND  (tbl_Shp_Order_Dtl.Order_Dtl_Codes = 74)

> Best Regards,

> Muhammad T. Alam

An update statement can only work on one table. If you want to update
multiple tables, you have two choices (a) use stored procedures that wrap
the various update statements or (b) use triggers.
--
HTH,
SriSamp
Please reply to the whole group only!

Quote:> Dear All,
> How can I update multiple tables by using single update query.

> This is not working... Please help

>   UPDATE   tbl_Shp_Order_Dtl, tbl_Cmp_Product
>   SET    tbl_Shp_Order_Dtl.Company_Codes=tbl_Cmp_Product.Company_Codes,
>      tbl_Shp_Order_Dtl.W_Cost=tbl_Cmp_Product.W_Cost,
>     tbl_Cmp_Product.Quantity_Sold=tbl_Cmp_Product.Quantity_Sold+1
>   FROM  tbl_Cmp_Product
>   INNER JOIN tbl_Shp_Order_Dtl
>   ON  tbl_Cmp_Product.Product_Codes = tbl_Shp_Order_Dtl.Product_Codes
>   AND  tbl_Cmp_Product.Characteristics = tbl_Shp_Order_Dtl.Characteristics
>   WHERE  (tbl_Cmp_Product.Company_Codes = 16)
>   AND  (tbl_Shp_Order_Dtl.Order_Dtl_Codes = 74)

> Best Regards,

> Muhammad T. Alam