發表者討論內容

冷日
(冷日) 發表時間：2013/9/25 6:13

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]如何更新資料表欄位透過其他資料表（UPDATE FROM TABLE）

用其它的 Table 來更新資料(Update From Table)

UPDATE  table1
SET     table1.col = table2.col1
FROM    table2
WHERE   table2.oldCol = table1.col

原文出處：[MSSQL] 用其它的 Table 來更新資料(Update From Table) @ 菲力貓的程式設計 :: 痞客邦 PIXNET ::

UPDATE from SELECT using SQL Server

In SQL Server you can insert into a table using a select statement:

INSERT INTO table(col,col2,col3)
SELECT col,col2,col3 FROM other_table WHERE sql = 'cool'

How can I update via a select as well in a similar manner? I have a temporary table that has the values, and I want to update another table using those values.

Something like this:

UPDATE Table SET col1,col2
SELECT col1,col2 FROM other_table WHERE sql = 'cool'
WHERE Table.id = other_table.id

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

In SQL Server 2008 (or better), use MERGE

MERGE INTO "Table" T
   USING other_table S
      ON T.id = S.id
         AND S.sql = 'cool'
WHEN MATCHED THEN
   UPDATE
      SET col1 = S.col1,
          col2 = S.col2;

Alternatively:

MERGE INTO "Table" T
   USING (
          SELECT id, col1, col2
            FROM other_table
           WHERE sql = 'cool'
         ) S
      ON T.id = S.id
WHEN MATCHED THEN
   UPDATE
      SET col1 = S.col1,
          col2 = S.col2;

I'd modify Robin's excellent answer to the following:

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)

Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.
share|improve this answer

Another possibility not mentioned yet is to just chuck the SELECT statement itself into a CTE then Update the 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

This has the benefit that it is easy to run the SELECT statement on its own first to sanity check the results but it does requires you to alias the columns as above if they are named the same in source and target tables.

This also has the same limitation as the proprietary UPDATE ... FROM syntax shown in four of the other answers. If the source table is on the many side of a one to many join then it is undeterministic which of the possible matching joined records will be used in the Update (An issue that MERGE avoids by raising an error if there is an attempt to update the same row more than once).

One way

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'

UPDATE Table SET Col1=i.Col1 ,
Col2=i.Col2 FROM (SELECT Col1,Col2 FROM  other_table)i
where i.ID=Table.ID

For the record (and others searching like I was), you can do it in MySQL like this:

UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id

Using alias:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN
       table2 AS o
         ON t.id = o.id

This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).

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

The sample way to do it is:

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

I add this only so you can see a quick way to write it so that you can check what will be updated before doing the update.

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

here is another useful syntax:

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

it checks if it is null or not by using "WHERE EXIST"

原文出處：UPDATE from SELECT using SQL Server - Stack Overflow

回覆

冷日
(冷日) 發表時間：2014/1/23 7:00

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]SQL UPDATE FROM 的使用方法

SQL UPDATE FROM 的使用方法

在資料庫作架構修改時，時常會遇到的情況是要將一個Table裡資料複製到另一的Table資料。這時UPDATE FROM的語法就非常有效。
應該會有更多不同情況下會用的這語法，一時想不起來。
使用方法是這樣的：
假設有，

一個產品的Table叫做Product
另一個Table也是放產品的資料，但在架構修改後就不再使用了，叫OldProduct
他們都有一個PK叫做SerialNumber

現在要把OldProduct中的一個ProductCategory複製到Product中對應資料的ProductCategoryCode欄位

UPDATE Product
SET ProductCategoryCode = o.ProductCategory
FROM OldProduct o JOIN Product p

ON o.SerialNumber = p.SerialNumber

這樣就行了，這等於就是將一個UPDATE跟一個SELECT併在一起，超方便。

原文出處：青蛙跳跳: SQL UPDATE FROM 的使用方法

回覆

冷日
(冷日) 發表時間：2014/1/23 7:02

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]SQL 從另一個表格更新資料

SQL 從另一個表格更新資料

Update data in one table with data from another table.
假設 History table 只有 Product_id, 現在新增 Product_Name column, 但資料再另一個 table 有, 要怎樣更新.

以往使用 MS SQL 做這件事非常簡單, 語法也很易懂.

update history
set product_name=product.product_name
from history, product
where history.product_id=product.product_id

如果是 Oracle :

update history h
set product_name=(select product_name from product p where p.product_id=h.product_id)
WHERE EXISTS( SELECT product_name FROM product p where p.product_id=h.product_id)

原文出處：
SQL 從另一個表格更新資料 @ 老爹寫程式 :: 痞客邦 PIXNET ::

回覆

冷日
(冷日) 發表時間：2016/2/21 15:26

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]mysql update column with value from another table

mysql update column with value from another table

I have two tables, both looking like

id  name  value
===================
1   Joe     22
2   Derk    30

I need to copy the value of value from tableA to tableB based on check name in each table.

Any tips for this UPDATE statement?

In addition to this answer if you need to change t1.value according to t2.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

you need to join the two tables:

for instance you want to copy the value of name from tableA into tableB where they have the same ID

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.id = t2.id
SET t1.name = t2.name
WHERE t2.name = 'Joe'

UPDATE 1

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.id = t2.id
SET t1.name = t2.name

UPDATE 2

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.name = t2.name
SET t1.value = t2.value

Second possibility is,

UPDATE TableB
SET TableB.value = (
    SELECT TableA.value
    FROM TableA
    WHERE TableA.name = TableB.name
);

原文出處：sql - mysql update column with value from another table - Stack Overflow

回覆

冷日
(冷日) 發表時間：2018/6/14 13:36

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]mysql update 不能直接使用 select 的結果: mysql update不能直接使用select的結果
在sql server中，我們可是使用以下update語句對表進行更新：
update a set a.xx= (select yy from b) ;
但是在mysql中，不能直接使用set select的結果，必須使用inner join：
update a inner join (select yy from b) c set a.xx = c.yy

例：
update mb_tariff a inner join
mb_tariff_temp b set a.payment = b.payment
where a.mybus_id = b.mybus_id
and a.tariff_id = b.tariff_id

自行示例：
UPDATE USER INNER JOIN (SELECT id,account FROM USER) c SET user.phone=c.account WHERE user.id = c.id

原文出處：mysql update不能直接使用select的结果 - 蚂蚁力量 - 博客园