Câu hỏi Làm thế nào để cập nhật từ một SELECT trong SQL Server?


Trong Máy chủ SQL, có thể insert vào một bảng bằng cách sử dụng SELECT tuyên bố:

INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3 
FROM other_table 
WHERE sql = 'cool'

Nó cũng có thể cập nhật thông qua một SELECT? Tôi có một bảng tạm thời chứa các giá trị, và muốn cập nhật một bảng khác bằng cách sử dụng các giá trị đó. Có lẽ một cái gì đó như thế này:

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

3038
2018-02-25 14:36


gốc


Nhận xét của Andy là hoàn toàn hợp lệ. Tôi chuyển đổi trang web mysql của tôi sang PDO gần đây nghĩ rằng tôi bây giờ bằng cách nào đó an toàn từ các cuộc tấn công tiêm. Đó là chỉ trong quá trình tôi nhận ra rằng một số báo cáo sql của tôi vẫn được xây dựng bằng cách sử dụng đầu vào của người dùng. Sau đó tôi đã cố định bằng cách sử dụng các câu lệnh đã chuẩn bị. Đối với một người mới hoàn thành, nó không hoàn toàn rõ ràng rằng có một sự phân biệt như nhiều chuyên gia ném ra các bình luận về cách sử dụng PDO nhưng không chỉ định t - Zafar Kurbonov


Các câu trả lời:


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'

4463
2018-02-25 14:39



Nếu bạn đang chỉnh sửa liên kết giữa các bảng (SET Table.other_table_id = @NewValue) sau đó thay đổi câu lệnh ON thành một cái gì đó như ON Table.id = @IdToEdit AND other_table.id = @NewValue - Trisped
Không phải cái này thiếu WHERE khoản trong câu hỏi? Tôi không có máy chủ trên hệ thống này để kiểm tra nhưng bạn sẽ không thể thêm máy chủ đó vào ON như: ON Table.id = other_table.id AND other_table.sql='cool'? Hay tôi hiểu sai câu hỏi? - J V
Điều này hoạt động bằng cách sử dụng UPDATE để lặp qua INNER JOIN. Như vậy các hàm ON như mệnh đề WHERE của bạn và INNER JOIN bỏ qua các bản ghi không được tìm thấy trong bảng JOINed. Thêm mệnh đề WHERE cũng sẽ giới hạn tập kết quả của bảng JOINed. @Roger Ray phiên bản nào của MySQL và truy vấn của bạn là gì, vì hàm DOES này hoạt động như đã nêu. - fyrye
@RogerRay, câu hỏi này là về Microsoft SQL Server. Thật không may, cú pháp giữa các triển khai SQL khác nhau có thể khác nhau. - Charles Wood
Có liên quan một chút, tôi thường thích viết các truy vấn UPDATE của tôi như là các câu lệnh SELECT đầu tiên để tôi có thể xem dữ liệu sẽ được cập nhật trước khi tôi thực hiện. Sebastian bao gồm một kỹ thuật cho điều này trong một bài đăng blog gần đây: sqlity.net/en/2867/update-from-select - dennislloydjr


Trong SQL Server 2008 (hoặc tốt hơn), sử dụng 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;

Ngoài ra:

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;

672
2017-09-09 09:40



MERGE cũng có thể được sử dụng cho các bản ghi "Upserting"; đó là, UPDATE nếu hồ sơ phù hợp tồn tại, INSERT bản ghi mới nếu không tìm thấy kết quả phù hợp - brichins
Đây là khoảng 10x nhanh hơn so với bản cập nhật tương đương ... tuyên bố tham gia cho tôi. - Paul Suart
MERGE cũng có thể được sử dụng để XÓA. Nhưng hãy cẩn thận với MERGE vì bảng TARGET không thể là một bảng từ xa. - Möoz
Hợp nhất lỗi: mssqltips.com/sqlservertip/3074/… - Simon D
@SimonD: chọn bất kỳ từ khóa SQL Server nào và bạn sẽ tìm thấy lỗi. Điểm của bạn? Tôi cược có nhiều lỗi hơn (và nhiều lỗi cơ bản hơn) liên kết với UPDATE hơn MERGE, mọi người đã học cách sống với họ và họ trở thành một phần của cảnh quan ('tính năng'). Xem xét các blog không tồn tại khi UPDATE là đứa trẻ mới trên khối. - onedaywhen


UPDATE table 
SET Col1 = i.Col1, 
    Col2 = i.Col2 
FROM (
    SELECT ID, Col1, Col2 
    FROM other_table) i
WHERE 
    i.ID = table.ID

524
2018-01-22 17:47



Đến nay là đơn giản nhất! Tuy nhiên bạn bỏ lỡ ID trường từ SELECT bên trong. Bạn sẽ cần điều này cho mệnh đề WHERE để làm việc - John Doherty
Điều này sẽ có xu hướng làm việc trên hầu như tất cả các DBMS có nghĩa là tìm hiểu một lần, thực hiện ở khắp mọi nơi. Nếu điều đó quan trọng đối với bạn hơn hiệu suất, bạn có thể thích câu trả lời này, đặc biệt nếu bản cập nhật của bạn là một phần tắt để sửa một số dữ liệu. - Alan Macdonald
@ dotnetN00b stackoverflow.com/a/2334741/206730 không tốt cho bạn? - Kiquenet


Tôi muốn sửa đổi Câu trả lời xuất sắc của Robin theo sau:

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
)

Nếu không có mệnh đề WHERE, bạn sẽ ảnh hưởng đến các hàng thậm chí không cần bị ảnh hưởng, mà có thể (có thể) gây ra chỉ số tính toán lại hoặc kích hoạt lửa mà thực sự không nên bị sa thải.


253
2017-09-08 21:20



Điều này giả định không có cột nào có giá trị mặc định. - Martin Smith
Bạn nói đúng, tôi đã gõ ví dụ bằng tay. Tôi đã thêm một mệnh đề thứ ba và thứ tư vào câu lệnh where để xử lý điều đó. - quillbreaker
WHERE EXISTS(SELECT T1.Col1, T1.Col2 EXCEPT SELECT T2.Col1, T2.Col2)) ngắn gọn hơn. - Martin Smith
không nên tuyên bố cũng chứa hai trong mệnh đề where? (other_table.col1 là null và table.col1 không phải là null) hoặc (other_table.col2 là null và table.col2 không phải là null) - Barka
Phụ thuộc vào nếu bạn muốn thay thế null trong đích với null từ nguồn. Thường xuyên, tôi không. Nhưng nếu bạn làm, Martin xây dựng mệnh đề where là điều tốt nhất để sử dụng. - quillbreaker


Một chiều

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'

180
2018-02-25 14:41





Một khả năng khác chưa được đề cập đến là chỉ cần mâm cặp SELECT tuyên bố chính nó thành một CTE và sau đó cập nhật 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

Điều này có lợi ích là nó rất dễ dàng để chạy SELECT tuyên bố trên đầu tiên của riêng mình để sanity kiểm tra kết quả, nhưng nó đòi hỏi bạn phải bí danh các cột như trên nếu chúng được đặt tên giống nhau trong bảng nguồn và đích.

Điều này cũng có cùng giới hạn như quyền sở hữu UPDATE ... FROM cú pháp được hiển thị trong bốn câu trả lời khác. Nếu bảng nguồn ở bên nhiều của một phép nối một-nhiều thì không xác định được các bản ghi có thể khớp nào sẽ được sử dụng trong Update (một vấn đề mà MERGE tránh bằng cách tăng lỗi nếu có cố gắng cập nhật cùng một hàng nhiều lần).


140
2017-11-06 00:18



có ý nghĩa nào của cái tên không CTE ? - Raptor
@ShivanRaptor - Đây là từ viết tắt của Biểu thức bảng chung. Chỉ là một bí danh tùy ý trong trường hợp này. - Martin Smith
Điều này cũng hoạt động tốt với nhiều CTE: ;WITH SomeCompexCTE AS (...), CTEAsAbove AS (SELECT T1.Col1,... FROM T1 JOIN SomeComplexCTE...) UPDATE CTEAsAbove SET Col1=_Col1, ... - VeeTheSecond


Đối với hồ sơ (và những người khác đang tìm kiếm như tôi), bạn có thể làm điều đó trong MySQL như thế này:

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

95
2017-10-05 14:20



Điều này làm việc tốt. Tôi đã thử tất cả các truy vấn trên, tất cả đều cho một hoặc một loại lỗi khác.
Cảm ơn bạn! Tôi biết đây là cũ, nhưng chỉ muốn nói điều này làm việc cho tôi. Máy chủ của tôi sẽ không cho phép FROM được sử dụng trong câu lệnh UPDATE. Vì vậy, tất cả các câu trả lời liên quan đến mệnh đề FROM trả lại một lỗi cú pháp. Cách này làm việc hoàn hảo. Nhiều đánh giá cao. - Todd Withers
Tại sao điều này lại được bình chọn như vậy thường xuyên? Sẽ là như vậy nếu tôi đăng cho mã ruby ​​hồ sơ nếu ai đó yêu cầu C # ... - isHuman
@isHuman Nó là rất phổ biến để đạt được trong một câu hỏi sql-server khi bạn tìm kiếm câu trả lời của tôi-sql (và ngược lại). Nó hoàn toàn khác với việc đăng câu trả lời ruby ​​trong câu hỏi c #. - Gabriel


Sử dụng bí danh:

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

78
2018-05-23 13:06





Cách đơn giản để làm điều đó là:

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

61
2017-11-14 13:17



Của bạn được định dạng tốt hơn; Ngoài ra, khi sử dụng một subselect, của bạn (và của Adrian) làm việc đáng tin cậy hơn so với định dạng khác. Cảm ơn bạn đã đăng câu trả lời của mình. - Ben West
Đây không phải là cú pháp Máy chủ SQl và nó sẽ không hoạt động trong máy chủ SQL - HLGEM