Pārējos rakstus var lasīt SQL pamatos.
SQL MERGE teikums ir visu pārējo DML teikumu maisījums. Tajā noteikti ir apakšvaicājums, tātad SELECT, tajā var būt gan INSERT, gan DELETE, gan UPDATE daļas. Tā galvenā doma un sūtība ir lielu datu kopu atjaunošana, kur piemēram, mums periodiski atnāk jauna datu kopa un tad atkarībā no tās vecajā datu kopā ir ieraksti jāpieliek klāt, jākoriģē vai jādzēš. Protams, ka to vienmēr var panākt 3 reiz skanējot cauri gan veco un jauno datu kopu un katrā reizē attiecīgi ierakstu pievienot ar INSERT, koriģēt ar UPDATE vai dzēst ar DELETE, taču ar MERGE to var panākt vienā piegājienā, kas, protams, ir gan uzskatāmāk, gan vispārīgā gadījumā ātrāk.
Šoreiz, lai dzīve nešķistu pārāk vienmuļa un Oracle orientēta 😉 sākšu ar piemēru no Microsoft SQL Servera. Tiesa gan MERGE ir pieejams tikai jaunajā 2008 versijā, kas tika acīmredzot ar pompu un varoņiem pārnestā nozīmē nopārdota “kamēr lācis vēl mežā”, un līdz ar to oficiāli kā produkcijas versija vēl joprojām nav pieejama. Tai pašā laikā kaut kādas tādas kā betas (Community Technology Preview) ir pieejamas un pat strādā, kā arī šai versijai eksistē dokumentācija, tāpēc piemērus izveidot var. Tādējādi šis pie reizes būs raksts, kas apskatīs vienu no SQL Server 2008 jaunajām iespējām.
SQL Server vienkāršota MERGE sintakse ir šāda:
MERGE INTO <mērķa tabula>
USING <izejas dati>
ON <salīdzināšanas nosacījums>
WHEN MATCHED THEN <darbības ja nosacījums patiess>
WHEN TARGET NOT MATCHED THEN <darbības, ja nosacījums aplams mērķa datos>
WHEN SOURCE NOT MATCHED THEN <darbības ja nosacījums aplams izejas datos>
Obligātais atslēgas vārds MERGE, protams, ir nepieciešams. Tālāk neobligāti var rakstīt atslēgas vādu INTO un pēc tam seko tabulas nosaukums, kurā MERGE tiks veikts. Tātad tā ir tabula, kurā tiks pievienoti, koriģēti vai dzēsti dati.
Šajā klauzā tiek norādīts, no kurienes dati tiks ņemti. Pašā triviālākajā gadījumā šeit var norādīt vienkārši tabulas vārdu. Protams, nekas nekavē šeit lietot arī skatījumu vai SELECT teikumu (derived table), tikai tam ir jābūt pieliktam aizstājējvārdam. Tātad šī lūk ir Merge teikuma Select daļa, par ko tika runāts augstāk, jo, pat ja Jūs lietojat pašu triviālāko gadījumu, t.i. izmantojat tabulu, vienalga tas faktiski nozīmē SELECT * FROM norādītā tabula.
ON <salīdzināšanas nosacījums>
Salīdzināšanas nosacījums ir tā lieta, kas noteiks, kuras rindas mērķa tabulā tiks aiztiktas, kuras nē. Salīdzināšanas nosacījums var būt jebkāds, taču jāatceras ļoti būtiska lieta – MERGE teikums katru rindu mērķa tabulā var 1) vai nu koriģēt vai dzēst 2) darīt to ne vairāk kā vienu reizi. Tas nozīmē, ka nosacījumam ir jābūt tādam, ka katram ierakstam mērķa tabulā atbilst ne vairāk kā viens ieraksts izejas datos. Tiklīdz kā tādu būs vairāk, Jūs iegūsiet kļūdu.
WHEN MATCHED THEN <darbības ja nosacījums patiess>
Šeit tiek rakstītas darbības (Update vai Delete), kas notiek tad, ja salīdzināšanas nosacījums ir izpildījies. Parastajā scenārijā (periodiska mērķa datu atjaunošana) tas nozīmē, ka mērķa tabulā ieraksti tiek koriģēti. Tagad beidzot ķeramies klāt pie piemēriem – tātad mums ir 2 tabulas, personals, kas būs mērķa tabula, un personals_src, kas tiks izmantota kā izejas datu tabula.
DROP TABLE personals;
DROP TABLE personals_src;
CREATE TABLE personals (
pers_id INTEGER NOT NULL PRIMARY KEY IDENTITY,
prs_vards VARCHAR(40),
prs_uzvards VARCHAR(40),
prs_alga NUMERIC(10, 2),
prs_statuss VARCHAR(1));
CREATE TABLE personals_src (
vards VARCHAR(40),
uzvards VARCHAR(40),
alga NUMERIC(10, 2));
INSERT INTO personals (prs_vards, prs_uzvards, prs_alga, prs_statuss)
VALUES ('JĀNIS', 'BĒRZIŅŠ', 1234.56, 'S');
INSERT INTO personals (prs_vards, prs_uzvards, prs_alga, prs_statuss)
VALUES ('JĀNIS', 'KALNIŅŠ', 570, 'N');
INSERT INTO personals (prs_vards, prs_uzvards, prs_alga, prs_statuss)
VALUES ('PĒTERIS', 'ZIRNIS', 680, 'S');
INSERT INTO personals (prs_vards, prs_uzvards, prs_alga, prs_statuss)
VALUES ('MAIJA', 'ZINĪTE', 700, 'N');
INSERT INTO personals_src VALUES ('JĀNIS', 'BĒRZIŅŠ', 1200.00);
INSERT INTO personals_src VALUES ('JĀNIS', 'KALNIŅŠ', 570);
INSERT INTO personals_src VALUES ('ANNA', 'LEJA', 680);
Piemērs 1. Tabulas personals sākotnējie dati
SELECT * FROM personals; 1 JĀNIS BĒRZIŅŠ 1234.56 S 2 JĀNIS KALNIŅŠ 570.00 N 3 PĒTERIS ZIRNIS 680.00 S 4 MAIJA ZINĪTE 700.00 N
Piemērs 2. Ja darbinieka vārds un uzvārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā. Šis Merge faktiski ne ar ko neatšķiras no vienkārša Update.
MERGE INTO personals USING personals_src ON prs_vards = vards AND prs_uzvards = uzvards WHEN MATCHED THEN UPDATE SET prs_alga = alga; (2 row(s) affected) SELECT * FROM personals; 1 JĀNIS BĒRZIŅŠ 1200.00 S 2 JĀNIS KALNIŅŠ 570.00 N 3 PĒTERIS ZIRNIS 680.00 S 4 MAIJA ZINĪTE 700.00 N
Redzem, ka ir atlasīti četri ieraksti, kur pirmajam un otrajam ir mainījusies alga. Pirms katrā nākošā piemēra par Merge ir jāizpilda atkal tabulu nomešanas un izveides skripts, lai iegūtu sākotnējos datus.
Piemērs 3. Ja darbinieka vārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā. Redzam, ka nosacījums ir pārāk vājš un iegūstam kļūdu, jo atbilstoši šādam nosacījuma mērķa tabulā ieraksts ir jākoriģē vairākas reizes.
MERGE INTO personals USING personals_src ON prs_vards = vards WHEN MATCHED THEN UPDATE SET prs_alga = alga; Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
WHEN MATCHED klauza var būt ne vairāk kā 2 reizes, pie tam vienai tādā gadījumā ir jābūt Update un otrā Delete. Tādā gadījumā pirmajai klauzai jāpievieno papildus nosacījums, kas ierobežo koriģējamo vai dzēšamo datu kopu.
Piemērs 4. Ja darbinieka vārds un uzvārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā gadījumā, ja darbinieks vēl strādā (statuss S). Ja darbinieks vairs nestrādā, tad dzēšam šādu ierakstu ārā.
MERGE INTO personals USING personals_src ON prs_vards = vards AND prs_uzvards = uzvards WHEN MATCHED AND prs_statuss = 'S' THEN UPDATE SET prs_alga = alga WHEN MATCHED THEN DELETE; (2 row(s) affected) SELECT * FROM personals; 1 JĀNIS BĒRZIŅŠ 1200.00 S 3 PĒTERIS ZIRNIS 680.00 S 4 MAIJA ZINĪTE 700.00 N
Šajā piemērā redzējām, ka Update teikumam ir papildus nosacījums – mērķa tabulā laukam prs_statuss ir jābūt vērtībai S. Tie, kas neatbilst šim nosacījumam, netiek vis koriģēti, bet dzēsti – attiecīgi tabulā personals ir vairs tikai 3 ieraksti nevis 4.
WHEN TARGET NOT MATCHED THEN <darbības, ja nosacījums aplams mērķa datos>
Atslēgas vārds TARGET ir neobligāts. Šajā solī tiek veiktas darbības, kas notiek tad, ja ON klauzā definētais nosacījums neizpildās uz mērķa tabulu, tas ir mērķa tabulā nav iespējams atrast tādu ierakstu, kāds ir izejas datos. Vienīgais, ko šeit var darīt, tas ir pievienot šādus ierakstus mērķa tabulā, kas parasti arī ir normālais scenārijs. Šāda klauza vienā Merge teikumā var būt ne vairāk kā viena un kā redzams Insert klauzā netiek norādīts, kurā tabulā dati tiks pievienoti, jo to viennozīmīgi jau nosaka Merge Into klauza.
Piemērs 5. Ja darbinieks nav mērķa tabulā, bet ir izejas datos, tad tādu pievienojam. Faktiski neatšķiras no vienkāršas Insert klauzas.
MERGE INTO personals USING personals_src ON prs_vards = vards AND prs_uzvards = uzvards WHEN TARGET NOT MATCHED THEN INSERT (prs_vards, prs_uzvards, prs_alga, prs_statuss) VALUES (vards, uzvards, alga, 'S'); (1 row(s) affected) SELECT * FROM personals; 1 JĀNIS BĒRZIŅŠ 1234.56 S 2 JĀNIS KALNIŅŠ 570.00 N 3 PĒTERIS ZIRNIS 680.00 S 4 MAIJA ZINĪTE 700.00 N 5 ANNA LEJA 680.00 S
WHEN SOURCE NOT MATCHED THEN <darbības, ja nosacījums aplams izejas datos>
Šī klauza tiek izpildīta, ja izejas datos nav iespējams atrast ierakstus, kas ir mērķa datos. Līdzīgi kā WHEN MATCHED klauzai arī šī klauza var būt ne vairāk kā 2 reizes MERGE teikumā un, ja tā ir 2 reizes, tad pirmajā gadījumā ir jāpievieno papildus nosacījums, un vienā gadījumā ir jābūt Update, otrā Delete.
Piemērs 6. Ja darbinieks nav atrodams izejas datos un tas nestrādā, tad tādu dzēšam ārā, bet ja tas strādā, tad uzstādam statusu uz neskaidru (?).
MERGE INTO personals USING personals_src ON prs_vards = vards AND prs_uzvards = uzvards WHEN SOURCE NOT MATCHED AND prs_statuss = 'N' THEN DELETE WHEN SOURCE NOT MATCHED THEN UPDATE SET prs_statuss = '?'; (2 row(s) affected) SELECT * FROM personals; 1 JĀNIS BĒRZIŅŠ 1234.56 S 2 JĀNIS KALNIŅŠ 570.00 N 3 PĒTERIS ZIRNIS 680.00 ?
Kopsavilkums
Merge teikums ir jaunums vismaz priekš SQL Servera. To lielākoties ir vērts izmantot periodisku datu atjaunošanā, datu migrācijā un tamlīdzīgos scenārijos, kad atkarībā no nosacījumiem ir nepieciešams veikt vienlaicīgi vismaz divas no Insert, Update, Delete operācijām. Dažas raksturīgās iezīmes:
- tajā ir jābūt vismaz vienai no When Matched, When Target Not Matched, When Source Not Matched klauzām;
- maksimāli var būt 2 When Matched, 1 When Target Not Matched, 2 When Source Not Matched klauzas;
- katrs ieraksts tiek vai nu dzēsts, vai koriģēts vai pievienots, vai arī ar to nedara neko, bet ne vairāk kā vienu reizi.
Nākošajā piemērā ir maksimālais iespējamo klauzu skaits. Tātad:
- tiem darbiniekiem, kas ir abās kopās un strādā, tiek koriģētas algas;
- tie darbinieki, kas ir abās kopās un nestrādā, tiek dzēsti;
- tie darbinieki, kas ir izejas kopā, bet nav mērķa kopā, tiek tai pievienoti;
- tiem darbiniekiem, kas nav izejas kopā, bet ir mērķa kopā un statuss ir “strādā”, tiek uzstādīts jauns statuss “neskaidrs”;
- tie darbinieki, kas nav izejas kopā, bet ir mērķa kopā un statuss ir “nestrādā”, tiek dzēsti.
Piemērs 7. Apvieno visu iepriekšējo piemēru funkcionalitāti.
MERGE INTO personals USING personals_src ON prs_vards = vards AND prs_uzvards = uzvards WHEN MATCHED AND prs_statuss = 'S' THEN UPDATE SET prs_alga = alga WHEN MATCHED THEN DELETE WHEN TARGET NOT MATCHED THEN INSERT (prs_vards, prs_uzvards, prs_alga, prs_statuss) VALUES (vards, uzvards, alga, 'S') WHEN SOURCE NOT MATCHED AND prs_statuss = 'N' THEN DELETE WHEN SOURCE NOT MATCHED THEN UPDATE SET prs_statuss = '?'; (5 row(s) affected) SELECT * FROM personals; 1 JĀNIS BĒRZINŠ 1200.00 S 3 PĒTERIS ZIRNIS 680.00 ? 5 ANNA LEJA 680.00 S
Turpmākā lasāmviela
- SQL Server Merge sintakse;
- Oracle SQL Merge sintakse.
Datubāzes 