MySQL - UPDATE zapytanie oparte na zapytaniu SELECT


501

Muszę sprawdzić (z tej samej tabeli), czy istnieje związek między dwoma zdarzeniami na podstawie daty i godziny.

Jeden zestaw danych będzie zawierał datę końcową niektórych zdarzeń, a drugi zestaw danych będzie zawierał datę początkową innych zdarzeń.

Jeśli pierwsze wydarzenie zakończy się przed drugim, chciałbym je połączyć.

Do tej pory mam:

SELECT name as name_A, date-time as end_DTS, id as id_A 
FROM tableA WHERE criteria = 1


SELECT name as name_B, date-time as start_DTS, id as id_B 
FROM tableA WHERE criteria = 2

Następnie dołączam do nich:

SELECT name_A, name_B, id_A, id_B, 
if(start_DTS > end_DTS,'VALID','') as validation_check
FROM tableA
LEFT JOIN tableB ON name_A = name_B

Czy mogę, na podstawie mojego pola validation_check, uruchomić zapytanie UPDATE z zagnieżdżonym SELECT?


2
Nie jestem pewien, o co pytasz? Czy próbujesz dowiedzieć się, jak wykonać aktualizację za pomocą SQL Select?
RiddlerDev,

Odpowiedzi:


811

Możesz to zrobić na dwa sposoby:

Aktualizacja dołączenia MySQL:

UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

Składnia ANSI SQL:

UPDATE tableA SET validation_check = 
    (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
        FROM tableA
        INNER JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Wybierz, który z nich wydaje ci się najbardziej naturalny.


92
Pierwsza forma (aktualizacja z dołączeniem) będzie znacznie wydajniejsza niż druga. Pierwszy wykonałby pojedyncze sprzężenie, podczas gdy drugi wykonałby zapytanie wyboru dla każdego wiersza tabeli A.
ColinM,

16
W pierwszym przykładzie nie powinieneś używać łączenia wewnętrznego? Czy lewe połączenie nie spowoduje, że parametr validation_check będzie ustawiony na null dla rekordów tableA bez odpowiedniego rekordu tableB?
Cerin,

3
@Cerin tak, co mi się przydarzyło. To powinno być wewnętrzne połączenie! Lub po prostu zostaw to jako dołączenie. Jeśli nie masz połączenia wewnętrznego, lewe połączenie oznacza, że ​​wszystkie rekordy tableA zostaną zaktualizowane! To jest bardzo niebezpieczne!
CMCDragonkai

10
Dzięki za pierwsze (y). Chociaż ta odpowiedź ma ponad 7 lat. Nie mogę uwierzyć, dlaczego nikt nie stwierdził, że drugi nie działa w niektórych bazach danych, takich jak MySQL. Otrzymasz ten błąd:You can't specify target table ___ for update in FROM clause SQL.sql
Jugali Lakota

1
Zauważ też, że do niedawna pierwsze podejście nie działało z MySQL kiedy tableA= tableB. Jesteś zmuszony utworzyć tymczasową tabelę do przechowywania wyniku pośredniego. (musiał napisać podobne zapytanie do skryptu migracji)
svvac

301
UPDATE
    `table1` AS `dest`,
    (
        SELECT
            *
        FROM
            `table2`
        WHERE
            `id` = x
    ) AS `src`
SET
    `dest`.`col1` = `src`.`col1`
WHERE
    `dest`.`id` = x
;

Mam nadzieję, że to Ci odpowiada.


5
To zdecydowanie najszybsze zapytanie. Edycja: Mając dest z 22 000 wierszy i src z 4K, ukończenie zajęło mniej niż 1 sekundę, a najwyższa odpowiedź ponad 60 sekund.
Chris Dev,

1
Tak, to najszybsze zapytanie, BTW, możesz również dodać klauzulę WHERE
robinmag

1
najszybsza nie zawsze oznacza dobrą, ta aktualizacja nie będzie działać we wszystkich przypadkach, w rzeczywistości w niektórych przypadkach będziesz mieć nieoczekiwane zachowanie, to wszystko, dlaczego nie postawić warunku i to nie jest połączenie
Emiliano

114

Łatwe w MySQL:

UPDATE users AS U1, users AS U2 
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id

58

Jeśli ktoś chce zaktualizować dane z jednej bazy danych do innej, bez względu na to, do której tabeli jest kierowany, muszą istnieć pewne kryteria.

Ten jest lepszy i czysty dla wszystkich poziomów:

UPDATE dbname1.content targetTable

LEFT JOIN dbname2.someothertable sourceTable ON
    targetTable.compare_field= sourceTable.compare_field
SET
    targetTable.col1  = sourceTable.cola,
    targetTable.col2 = sourceTable.colb, 
    targetTable.col3 = sourceTable.colc, 
    targetTable.col4 = sourceTable.cold 

Traaa! Działa świetnie!

Dzięki powyższemu zrozumieniu możesz modyfikować ustawione pola i kryteria „włączone”, aby wykonać swoją pracę. Możesz także wykonać kontrole, a następnie pobrać dane do tabel tymczasowych, a następnie uruchomić aktualizację, korzystając z powyższej składni, zastępując nazwy tabel i kolumn.

Mam nadzieję, że to zadziała, jeśli nie, daj mi znać. Napiszę dokładne zapytanie.


24
UPDATE 
  receipt_invoices dest,
  (
    SELECT 
      `receipt_id`,
      CAST((net * 100) / 112 AS DECIMAL (11, 2)) witoutvat 
    FROM
      receipt 
    WHERE CAST((net * 100) / 112 AS DECIMAL (11, 2)) != total 
      AND vat_percentage = 12
  ) src 
SET
  dest.price = src.witoutvat,
  dest.amount = src.witoutvat 
WHERE col_tobefixed = 1 
  AND dest.`receipt_id` = src.receipt_id ;

Mam nadzieję, że to pomoże ci w przypadku, gdy musisz dopasować i zaktualizować dwie tabele.


12

Znalazłem to pytanie, szukając własnego rozwiązania bardzo złożonego łączenia. Jest to alternatywne rozwiązanie bardziej złożonej wersji problemu, które moim zdaniem może być przydatne.

Musiałem wypełnić pole product_id w tabeli działań, gdzie działania są ponumerowane w jednostce, a jednostki są ponumerowane na poziomie (identyfikowanym za pomocą ciągu ?? N), tak aby można było zidentyfikować działania za pomocą SKU, tj. L1U1A1. Te jednostki SKU są następnie przechowywane w innej tabeli.

Zidentyfikowałem następujące elementy, aby uzyskać listę id_działania w stosunku do id_produktu: -

SELECT a.activity_id, w.product_id 
  FROM activities a 
  JOIN units USING(unit_id) 
  JOIN product_types USING(product_type_id) 
  JOIN web_products w 
    ON sku=CONCAT('L',SUBSTR(product_type_code,3), 'U',unit_index, 'A',activity_index)

Stwierdziłem, że jest to zbyt skomplikowane, aby włączyć je do SELECT w mysql, więc utworzyłem tabelę tymczasową i połączyłem ją z instrukcją aktualizacji:

CREATE TEMPORARY TABLE activity_product_ids AS (<the above select statement>);

UPDATE activities a
  JOIN activity_product_ids b
    ON a.activity_id=b.activity_id 
  SET a.product_id=b.product_id;

Mam nadzieję, że ktoś uzna to za przydatne


dwa zapytania mogą nie dawać spójnego wyniku w środowisku wielowątkowym.
donald

7
UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];

4

Możesz aktualizować wartości z innej tabeli za pomocą łączenia wewnętrznego w ten sposób

UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.column1_name] = t2.[column1_name] SET t1.[column2_name] = t2.column2_name];

Śledź tutaj, aby dowiedzieć się, jak korzystać z tego zapytania http://www.voidtricks.com/mysql-inner-join-update/

lub możesz użyć select jako podkwerendy, aby to zrobić

UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];

zapytanie wyjaśnione szczegółowo tutaj http://www.voidtricks.com/mysql-update-from-select/


4

Możesz użyć:

UPDATE Station AS st1, StationOld AS st2
   SET st1.already_used = 1
 WHERE st1.code = st2.code

3

Dla tego samego stołu

UPDATE PHA_BILL_SEGMENT AS PHA,
     (SELECT BILL_ID, COUNT(REGISTRATION_NUMBER) AS REG 
       FROM PHA_BILL_SEGMENT
        GROUP BY REGISTRATION_NUMBER, BILL_DATE, BILL_AMOUNT
        HAVING REG > 1) T
    SET PHA.BILL_DATE = PHA.BILL_DATE + 2
 WHERE PHA.BILL_ID = T.BILL_ID;

1

Miałem problem ze zduplikowanymi wpisami w samej tabeli. Poniżej znajduje się podejście, które działało dla mnie. Jest również zalecany przez @sibaz.

Wreszcie rozwiązałem go, korzystając z poniższych zapytań:

  1. Wybrane zapytanie jest zapisywane w tabeli tymczasowej

    IF OBJECT_ID(N'tempdb..#New_format_donor_temp', N'U') IS NOT NULL
        DROP TABLE #New_format_donor_temp;
    
    select *
    into #New_format_donor_temp
    from DONOR_EMPLOYMENTS
    where DONOR_ID IN (
      1, 2
    )
    
    -- Test New_format_donor_temp
    -- SELECT *
    -- FROM #New_format_donor_temp;
  2. Tabela temp jest dołączana w zapytaniu dotyczącym aktualizacji.

    UPDATE de
    SET STATUS_CD=de_new.STATUS_CD, STATUS_REASON_CD=de_new.STATUS_REASON_CD, TYPE_CD=de_new.TYPE_CD
    FROM DONOR_EMPLOYMENTS AS de
      INNER JOIN #New_format_donor_temp AS de_new ON de_new.EMP_NO = de.EMP_NO
    WHERE
      de.DONOR_ID IN (
        3, 4
    )

Nie mam zbyt dużego doświadczenia z SQL, proszę doradzić jakieś lepsze podejście, jakie znasz.

Powyższe zapytania dotyczą serwera MySql.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.