Jak wstawić lub zaktualizować za pomocą pojedynczego zapytania?

Mam test tabeli mający kolumny id, który klucz podstawowy i auto inkrementowane i nazwa. Chcę wstawić nowy rekord, a jeśli tylko nie ma żadnych rekordów, na przykład

dane wejściowe to id = 30122, a name = John

jeśli istnieją rekordy o identyfikatorze 30122, zaktualizowałem kolumnę z nazwiskiem do Johna, jeśli nie ma żadnych rekordów, wstawiłem nowy rekord.

Mogę zrobić używając 2 zapytań takich jak

select * from test where id=30122

jeśli ma jakieś rekordy, mogę użyć update test set name='john' where id=3012

lub jeśli nie ma zapisów, mogę użyć

insert into test(name) values('john')

Ale chciałem użyć pojedynczego zapytania?

Czy ktoś może powiedzieć, czy to możliwe?

sql-server merge upsert

— SpringLearner
źródło

But I wanted to use single query?Czemu?

— Aaron Bertrand

@AaronBertrand Mój back-end jest rozwijany za pomocą java. Więc jeśli używam 2 qury, to muszę uderzyć DB 2 razy, więc jeśli można to zrobić za pomocą jednego zapytania, to po co używać 2 zapytań

— SpringLearner

Java nie obsługuje procedury składowanej ani pojedynczej partii z dwiema instrukcjami wymagającymi tylko jednego trafienia do bazy danych?

— Aaron Bertrand

@AaronBertrand czy możesz podać przykład, jak poradziłbyś sobie z tym z serwerem SQL 2008 lub nowszym?

— eaglei22

@ eaglei22 Chciałbym użyć drugiego przykładu w odpowiedzi vijayp poniżej. Nadal nie wybrałbym MERGEżadnej wersji, nawet SQL Server 2019. Trochę o tym tutaj .

— Aaron Bertrand

Odpowiedzi:

Możesz tego spróbować

IF EXISTS(select * from test where id=30122)
   update test set name='john' where id=3012
ELSE
   insert into test(name) values('john');

Inne podejście do lepszej wydajności to

update test set name='john' where id=3012
IF @@ROWCOUNT=0
   insert into test(name) values('john');

a także przeczytaj te złe nawyki, aby uruchomić prefiks schematu

— vijayp
źródło

Pierwszy przykład jest marnotrawny i często może prowadzić do impasu - w ogóle nie sugerowałbym tego.

— Aaron Bertrand

@AaronBertrand chcesz opracować? Dzięki

— Hexo

@SlapY Jasne, w pierwszym przykładzie mówisz: „Hej, SQL Server, czy jest wiersz z tym identyfikatorem?” SQL Server wyrusza w poszukiwaniu wiersza, być może za pomocą skanu, a następnie wraca z odpowiedzią. „No tak, użytkownik, mam wiersz z tym identyfikatorem!” Następnie mówisz: „Dobra, SQL Server, znajdź ten wiersz jeszcze raz , ale tym razem go zaktualizuj!” Czy widzisz, że dwukrotne wykonywanie wyszukiwania lub skanowania jest marnotrawstwem? Czy możesz sobie wyobrazić, co się stanie, jeśli inny użytkownik zapyta SQL Server o to samo pytanie dotyczące istnienia wiersza, zanim zaczniesz coś z tym robić?

— Aaron Bertrand

Dzięki, po prostu nie rozumiem, dlaczego pierwszy grozi impasem, a drugi nie? Oba składają się z wielu instrukcji, które można przechwycić, jeśli nie zostaną uruchomione z pełną blokadą. Czy się mylę?

— Hexo,

@ 0x25b3 Nie chodzi o to, że jedno jest zagrożone impasem, a drugie nie, to o to, że pierwszy przykład jest znacznie bardziej podatny na nie. W obu przypadkach powinieneś zawrzeć pełną i właściwą transakcję, ale ludzie tego nie robią, więc ...

— Aaron Bertrand

Zakładając SQL Server 2008 lub nowszy, możesz użyć MERGE:

Stół

CREATE TABLE dbo.Test
(
    id integer NOT NULL,
    name varchar(30) NULL,

    CONSTRAINT PK_dbo_Test__id
        PRIMARY KEY CLUSTERED (id)
);

Pytanie

MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING (VALUES (3012, 'john')) AS U (id, name)
    ON U.id = T.id
WHEN MATCHED THEN 
    UPDATE SET T.name = U.name
WHEN NOT MATCHED THEN
    INSERT (id, name) 
    VALUES (U.id, U.name);

SERIALIZABLEWskazówka jest wymagany do poprawnej pracy pod wysokim współbieżności .

Porównanie popularnych metod autorstwa Michaela J. Swarta można znaleźć tutaj:

Mythbusting: Jednoczesna aktualizacja / wstawianie rozwiązań

— Evaldas Buinauskas
źródło

Scalanie ma pewne problemy .

— vonPryz

link do mitów jest doskonały. Niezłe!

— JonnyRaa