Oracle: jak UPSERTOWAĆ (uaktualnić lub wstawić do tabeli?)
Operacja UPSERT albo aktualizuje lub wstawia wiersz w tabeli, w zależności od tego, czy tabela ma już wiersz pasujący do danych:
if table t has a row exists that has key X:
update t set mystuff... where mykey=X
else
insert into t mystuff...
Ponieważ Oracle nie ma konkretnego Oświadczenia UPSERT, jaki jest najlepszy sposób, aby to zrobić?
12 answers
Alternatywa dla MERGE ("staromodny sposób"):
begin
insert into t (mykey, mystuff)
values ('X', 123);
exception
when dup_val_on_index then
update t
set mystuff = 123
where mykey = 'X';
end;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-10-27 11:12:13
Instrukcja merge łączy dane między dwiema tabelami. Korzystanie z DUAL pozwala nam na użycie tego polecenia. Zauważ, że nie jest to chronione przed równoczesnym dostępem.
create or replace
procedure ups(xa number)
as
begin
merge into mergetest m using dual on (a = xa)
when not matched then insert (a,b) values (xa,1)
when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;
A B
---------------------- ----------------------
10 2
20 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-17 00:21:26
Powyższy podwójny przykład, który jest w PL / SQL był świetny, ponieważ chciałem zrobić coś podobnego, ale chciałem to klient side...so oto SQL, którego użyłem do wysłania podobnej instrukcji bezpośrednio z jakiegoś C #
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" )
Jednak z punktu widzenia C# zapewnia to wolniejsze niż wykonywanie aktualizacji i sprawdzanie, czy wiersze, których to dotyczy, były równe 0 i wykonywanie wstawiania, jeśli tak było.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-01-31 01:32:05
Inna alternatywa bez sprawdzania WYJĄTKÓW:
UPDATE tablename
SET val1 = in_val1,
val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%rowcount = 0 )
THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-10-28 14:37:32
- insert if not exists
- Aktualizacja:
INSERT INTO mytable (id1, t1) SELECT 11, 'x1' FROM DUAL WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-23 14:24:02
Żadna z udzielonych dotąd odpowiedzi nie jestBezpieczna w obliczu równoczesnego dostępu , Jak wskazano w komentarzu Tima Sylvestra, i będzie podnosić wyjątki w przypadku ras. Aby to naprawić, kombi insert/update musi być zapakowane w jakąś instrukcję loop, tak aby w przypadku wyjątku całość została powtórzona.
Na przykład, oto jak kod Grommita może być zawinięty w pętlę, aby był bezpieczny podczas jednoczesnego działania:PROCEDURE MyProc (
...
) IS
BEGIN
LOOP
BEGIN
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" );
EXIT; -- success? -> exit loop
EXCEPTION
WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
NULL; -- exception? -> no op, i.e. continue looping
WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
NULL; -- exception? -> no op, i.e. continue looping
END;
END LOOP;
END;
Uwaga: w trybie transakcji SERIALIZABLE
, który Nie polecam btw, może wpadniesz na
ORA-08177: nie można serializować dostępu do tej transakcji zamiast WYJĄTKÓW.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:54:48
Chciałbym Grommit odpowiedź, ale to wymaga dupe wartości. Znalazłem rozwiązanie, gdzie może się pojawić raz: http://forums.devshed.com/showpost.php?p=1182653&postcount=2
MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
INSERT ( CILT, SAYFA, KUTUK, MERNIS_NO)
VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-03 10:03:25
Uwaga dotycząca dwóch rozwiązań, które sugerują:
1) Insert, if exception then update,
LUB
2) Update, if SQL % rowcount = 0 then insert
Kwestia, czy najpierw wstawić lub zaktualizować, jest również zależna od aplikacji. Spodziewasz się więcej wstawek lub aktualizacji? Ten, który jest najbardziej prawdopodobne, aby odnieść sukces, powinien iść pierwszy.
Jeśli wybierzesz niewłaściwy, otrzymasz kilka niepotrzebnych odczytów indeksów. Nic wielkiego, ale wciąż coś do zastanów się.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-25 23:19:47
Używam pierwszej próbki kodu od lat. Notice notfound than count.
UPDATE tablename SET val1 = in_val1, val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
Poniższy kod jest prawdopodobnie nowym i ulepszonym kodem
MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT
VALUES (in_val1, in_val2, in_val3)
W pierwszym przykładzie aktualizacja dokonuje wyszukiwania indeksu. Musi, aby zaktualizować właściwy wiersz. Oracle otwiera Ukryty kursor, a my używamy go do zawijania odpowiedniej wstawki, więc wiemy, że wstawka stanie się tylko wtedy, gdy klucz nie istnieje. Ale insert jest niezależnym poleceniem i musi zrobić drugą / align = "left" / Nie znam wewnętrznego działania polecenia merge, ale ponieważ polecenie jest pojedynczą jednostką, Oracle może wykonać poprawną wstawkę lub aktualizację za pomocą jednego wyszukiwania indeksu.
Myślę, że scalanie jest lepsze, gdy trzeba wykonać pewne przetwarzanie, które oznacza pobranie danych z niektórych tabel i aktualizację tabeli, ewentualnie wstawianie lub usuwanie wierszy. Ale dla przypadku pojedynczego wiersza, możesz rozważyć pierwszy przypadek, ponieważ składnia jest bardziej powszechna.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-12 16:53:50
Przykład kopiowania i wklejania do wstawiania jednej tabeli do drugiej z MERGE:
CREATE GLOBAL TEMPORARY TABLE t1
(id VARCHAR2(5) ,
value VARCHAR2(5),
value2 VARCHAR2(5)
)
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE t2
(id VARCHAR2(5) ,
value VARCHAR2(5),
value2 VARCHAR2(5))
ON COMMIT DELETE ROWS;
ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id);
insert into t1 values ('a','1','1');
insert into t1 values ('b','4','5');
insert into t2 values ('b','2','2');
insert into t2 values ('c','3','3');
merge into t2
using t1
on (t1.id = t2.id)
when matched then
update set t2.value = t1.value,
t2.value2 = t1.value2
when not matched then
insert (t2.id, t2.value, t2.value2)
values(t1.id, t1.value, t1.value2);
select * from t2
Wynik:
- b 4 5
- c 3 3
- a 1 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-09 15:08:19
Spróbuj tego,
insert into b_building_property (
select
'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
from dual
)
minus
(
select * from b_building_property where id = 9
)
;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-27 07:46:39
Z http://www.praetoriate.com/oracle_tips_upserts.htm :
W Oracle9i, upsert może wykonać to zadanie jednym stwierdzeniem: "INSERT
FIRST WHEN
credit_limit >=100000
THEN INTO
rich_customers
VALUES(cust_id,cust_credit_limit)
INTO customers
ELSE
INTO customers SELECT * FROM new_customers;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-10 01:24:03