Doel

Gebruik de instructie UPDATE om bestaande waarden in een tabel of in de basistabel van een view of de hoofdtabel van een gematerialiseerde weergave.

Aanvullende onderwerpen

• Vereisten

• Syntaxis

• Semantiek

• Voorbeelden

Vereisten

Voor u om waarden in een tabel bij te werken, moet de tabel in uw eigen schema staan of u moet het objectprivilege UPDATE voor de tabel hebben.

Om bij te werken waarden in de basistabel van een weergave:

• U moet het objectprivilege UPDATE hebben voor de weergave, en

• Degene die eigenaar is van het schema dat de view bevat, moet het UPDATE objectprivilege op de basistabel hebben.

Met het UPDATE ANY TABLE systeemprivilege kunt u waarden in elke tafel of in de basistafel van elke weergave.

U moet ook het SELECT objectprivilege hebben voor het object dat u wilt bijwerken als:

• Het object bevindt zich op een externe database of

• De SQL92_SECURITY initialisatieparameter is ingesteld op TRUE en de UPDATE bewerking verwijst naar tabelkolommen, zoals de kolommen in een where_clause.

Syntaxis

update :: =

Beschrijving van de illustratie update.gif

(DML_table_expression_clause :: =, update_set_clause :: =, where_clause :: =, return_clause :: =, error_logging_clause :: =)

DML_table_expression_clause :: =

Beschrijving van de illustratie DML_table_expression_clause.gif

(subquery :: = – onderdeel van SELECT, subquery_restriction_clause :: =, table_collection_expression :: =)

subquery_restriction_clause :: =

Beschrijving van de illustratie subquery_restriction_clause.gif

table_collection_expression :: =

Beschrijving van de illustratie table_collection_expression.gif

update_set_clause :: =

Beschrijving van de illustratie update_set_clause. gif

where_clause :: =

Beschrijving van de illustratie where_clause.gif

return_clause :: =

Beschrijving van de illustratie return_clause.gif

error_logging_clause :: =

Beschrijving van de illustratie error_logging_clause.gif

Semantiek

hint

Geef een opmerking op die instructies doorgeeft aan de optimizer bij het kiezen van een uitvoeringsplan voor de instructie.

U kunt een parallelle hint direct achter het UPDATE trefwoord plaatsen om zowel de under liggende scan en UPDATE bewerkingen.

DML_table_expression_clause

De ONLY clausule is alleen van toepassing op views. Specificeer de ONLY syntaxis als de weergave in de UPDATE -clausule een weergave is die tot een hiërarchie behoort en u geen rijen wilt bijwerken van zijn subweergaven.

schema

Geef het schema op met het object dat moet worden bijgewerkt. Als u schema weglaat, gaat de database ervan uit dat het object zich in uw eigen schema bevindt.

table | bekijk | materialized_view | subquery

Specificeer de naam van de tabel, view, gematerialiseerde view of de kolommen die worden geretourneerd door een subquery die moet worden bijgewerkt. Als een UPDATE -instructie wordt afgegeven tegen een tabel, worden alle UPDATE -triggers geactiveerd die aan de tabel zijn gekoppeld.

• Als u view opgeeft, werkt de database de basistabel van de weergave bij.U kunt een weergave alleen bijwerken met INSTEAD OF triggers als de bepalende vraag van de weergave een van de volgende constructies bevat:

Een set-operator ADISTINCT-operator Een geaggregeerde of analytische functie AGROUPBY,ORDERBY,MODEL,CONNECTBY, ofSTARTWITH-clausule Een verzamelingsexpressie in eenSELECTlist Een subquery in eenSELECTlijst Een subquery aangeduid metWITH READ ONLYJoins, met enkele uitzonderingen , zoals gedocumenteerd in de Oracle Database Administrator’s Guide

• U kunt niet meer dan één basistabel bijwerken via een view.

• Bovendien, als de weergave is gemaakt met de WITH CHECK , dan kunt u de weergave alleen bijwerken als de resulterende gegevens voldoen aan de bepalende vraag van de weergave.

• Als table of de basistabel van view bevat een of meer domeinindexkolommen, dan voert deze instructie de juiste update-routine van het indextype uit.

• U kunt geen rijen bijwerken in een alleen-lezen opgebouwde weergave. Als u rijen bijwerkt in een beschrijfbare gematerialiseerde weergave, werkt de database de rijen bij vanuit de onderliggende containertabel. De updates worden echter overschreven bij de volgende vernieuwingsbewerking. Als u rijen bijwerkt in een bij te werken gematerialiseerde weergave die deel uitmaakt van een opgebouwde weergavegroep, dan werkt de database ook de overeenkomstige rijen in de hoofdtabel bij.

PARTITIE | SUBPARTITIE

Specificeer de naam van de partitie of subpartitie binnen table bedoeld voor updates. U hoeft de partitienaam niet op te geven bij het bijwerken van waarden in een gepartitioneerde tabel. In sommige gevallen kan het specificeren van de partitienaam echter efficiënter zijn dan een gecompliceerde where_clause .

dblink

Specificeer een volledige of gedeeltelijke naam van een databasekoppeling naar een database op afstand, waarbij t hij object is gelokaliseerd. U kunt een databasekoppeling alleen gebruiken om een extern object bij te werken als u de gedistribueerde functionaliteit van Oracle Database gebruikt.

Als u dblink, weglaat, gaat de database ervan uit dat het object is op de lokale database.

subquery_restriction_clause

Gebruik de subquery_restriction_clause om de subquery op een van de volgende manieren te beperken:

MET ALLEEN LEZEN Specificeer WITH READ ONLY om aan te geven dat de tabel of view niet kan worden bijgewerkt.

WITH CHECK OPTION Specificeer WITH CHECK OPTION om aan te geven dat Oracle Database alle wijzigingen in de tabel of view verbiedt die rijen die niet zijn opgenomen in de subquery. Bij gebruik in de subquery van een DML-instructie, kunt u deze clausule in een subquery specificeren in de FROM -component, maar niet in de subquery in de WHERE clausule.

CONSTRAINT-beperking Specificeer de naam van de CHECK OPTION beperking. Als u deze identifier weglaat, kent Oracle automatisch een naam toe aan de beperking in de vorm SYS_C n, waarbij n een geheel getal is dat de beperking naam uniek in de database.

table_collection_expression

De table_collection_expression laat u Oracle informeren dat de waarde van collection_expression moet worden behandeld als een tabel voor query- en DML-bewerkingen. De collection_expression kan een subquery, een kolom, een functie of een verzamelingsconstructor zijn. Ongeacht de vorm moet het een verzamelingswaarde retourneren, dat wil zeggen een waarde waarvan het type geneste tabel of varray is. Dit proces van het extraheren van de elementen van een collectie wordt collection unesting genoemd.

De optionele plus (+) is relevant als je de expressie TABLE samenvoegt met de bovenliggende tafel. De + creëert een buitenste samenvoeging van de twee, zodat de query rijen uit de buitenste tabel retourneert, zelfs als de verzamelingsexpressie null is.

U kunt een table_collection_expression gebruiken om rijen in de ene tabel bij te werken op basis van rijen uit een andere tabel. U kunt bijvoorbeeld vier driemaandelijkse verkooptabellen samenvoegen tot een jaarlijkse verkooptabel.

t_alias

Specificeer een correlatienaam (alias) voor de tabel, weergave of subquery waarnaar moet worden verwezen elders in de verklaring. Deze alias is vereist als de DML_table_expression_clause verwijst naar objecttypekenmerken of objecttypemethoden.

Beperkingen op de DML_table_expression_clause Deze clausule is onderhevig aan de volgende beperkingen:

• U kunt deze instructie niet uitvoeren als table of de basistabel van view bevat alle domeinindexen gemarkeerd met IN_PROGRESS of FAILED.

• Je kunt niet in een partitie invoegen als een van de betrokken indexpartities is gemarkeerd met UNUSABLE.

• Je kunt de order_by_clause niet specificeren in de subquery van de DML_table_expression_clause.

• Als u een index, indexpartitie of index-subpartitie specificeert die is gemarkeerd als UNUSABLE, dan zal de UPDATE -instructie mislukken, tenzij de SKIP_UNUSABLE_INDEXES sessieparameter is ingesteld op TRUE.

update_set_clause

Met update_set_clause kun je kolomwaarden instellen.

column

Specificeer de naam van een kolom van het object dat moet worden bijgewerkt. Als u een kolom van de tabel weglaat uit de update_set_clause, dan blijft de waarde van die kolom ongewijzigd.

Als column verwijst naar een LOB-objectkenmerk, dan moet u het eerst initialiseren met de waarde leeg of null. U kunt het niet bijwerken met een letterlijke. Als u een LOB-waarde bijwerkt met een andere methode dan een directe UPDATE SQL-instructie, moet u eerst de rij met de LOB vergrendelen. Zie for_update_clause voor meer informatie.

Als column deel uitmaakt van de partitioneringssleutel van een gepartitioneerde tabel, dan UPDATE zal mislukken als u een waarde in de kolom wijzigt die de rij naar een andere partitie of subpartitie zou verplaatsen, tenzij u rijverplaatsing inschakelt. Raadpleeg de row_movement_clause van CREATE TABLE of ALTER TABLE.

Bovendien, als column deel uitmaakt van de partitioneringssleutel van een lijst-gepartitioneerde tabel, dan zal UPDATE mislukken als u een waarde opgeeft voor de kolom die nog niet bestaat in de partition_value lijst van een van de partities.

subquery

Specificeer een subquery die exact één rij retourneert voor elke bijgewerkte rij.

• Als u specificeert slechts één kolom in de update_set_clause, dan kan de subquery slechts één waarde retourneren.

• Als u meerdere kolommen specificeert in de update_set_clause, dan moet de subquery net zoveel waarden retourneren als u gespecificeerde kolommen hebt opgegeven.

• Als de subquery geen rijen retourneert, dan moet de subquery kolom krijgt een null toegewezen.

• Als deze subquery verwijst naar externe objecten, dan is de UPDATE bewerking kan parallel worden uitgevoerd zolang het bestand referen ce keert niet terug naar een object in de lokale database. Als de subquery in de DML_table_expression_clause echter naar externe objecten verwijst, dan is de UPDATE bewerking wordt serieel uitgevoerd zonder kennisgeving.

U kunt de flashback_query_clause binnen de subquery gebruiken om met gegevens uit het verleden. Raadpleeg de flashback_query_clause van SELECT voor meer informatie over deze clausule.

expr

Specificeer een uitdrukking die wordt omgezet naar de nieuwe waarde die is toegewezen aan de corresponderende kolom.

STANDAARD Specificeer DEFAULT om de kolom in te stellen op de waarde die eerder als standaard was opgegeven waarde voor de kolom. Als er geen standaardwaarde voor de corresponderende kolom is opgegeven, stelt de database de kolom in op null.

Beperking bij bijwerken naar standaardwaarden U kunt DEFAULT niet specificeren als u werkt een weergave bij.

VALUE-clausule

Met de VALUE -clausule kunt u de volledige rij van een objecttabel specificeren.

Beperking op de VALUE-clausule U kunt deze clausule alleen specificeren voor een objecttabel.

where_clause

Met where_clause kunt u de rijen beperken die zijn bijgewerkt tot die waarvoor de opgegeven condition is waar. Als u deze clausule weglaat, werkt de database alle rijen in de tabel of view bij. Raadpleeg hoofdstuk 7, “Voorwaarden” voor de syntaxis van condition.

De where_clause bepaalt de rijen in welke waarden worden bijgewerkt. Als u de where_clause niet opgeeft, worden alle rijen bijgewerkt. Voor elke rij die voldoet aan de where_clause, worden de kolommen links van de gelijkheidsoperator (=) in de update_set_clause ingesteld op de waarden van de overeenkomstige uitdrukkingen aan de rechterkant van de operator. De expressies worden geëvalueerd terwijl de rij wordt bijgewerkt.

return_clause

De return-clausule haalt de rijen op die worden beïnvloed door een DML-instructie. U kunt deze clausule specificeren voor tabellen en gematerialiseerde weergaven en voor weergaven met een enkele basistabel.

Wanneer u op een enkele rij werkt, een DML-instructie met een returning_clause kan kolomuitdrukkingen ophalen met behulp van de betrokken rij, rowid en REFs naar de betrokken rij en deze opslaan in hostvariabelen of PL / SQL-variabelen.

Bij gebruik op meerdere rijen, een DML-instructie met de returning_clause slaat waarden op uit expressies, rowids en REFs met betrekking tot de betrokken rijen in bindarrays.

expr Elk item in de expr lijst moet een geldige expressiesyntaxis zijn.

IN DE INTO clausule geeft aan dat de waarden van de gewijzigde rijen moeten worden opgeslagen in de variabele (n) gespecificeerd in data_item lijst.

data_item Elk data_item is een hostvariabele of PL / SQL-variabele die de opgehaalde expr waarde opslaat.

Voor elke expressie in de RETURNING -lijst moet u een overeenkomstige type-compatibele PL / SQL-variabele of hostvariabele specificeren in de INTO lijst.

Beperkingen De volgende beperkingen zijn van toepassing op de RETURNING -clausule:

• De expr is als volgt beperkt:

  • Voor UPDATE en DELETE statements elk expr moet een eenvoudige uitdrukking zijn of een enkelvoudige verzamelde functie-uitdrukking. U kunt eenvoudige uitdrukkingen en enkelvoudige verzamelingsfunctie-uitdrukkingen niet combineren in dezelfde returning_clause. Voor INSERT -instructies moet elke expr een eenvoudige uitdrukking zijn. Geaggregeerde functies worden niet ondersteund in een INSERT statement RETURNING clausule.

  • Single- set verzamelde functie-expressies mogen het DISTINCT sleutelwoord niet bevatten.

• Als de expr lijst bevat een primaire sleutelkolom of een andere NOT NULL kolom, dan mislukt de update-instructie als de tabel een BEFORE UPDATE trigger die erop is gedefinieerd.

• U kunt de returning_clause voor een multi-insert.

• Je kunt deze clausule niet gebruiken met parallelle DML of met externe objecten.

• U kunt geen LONG typen ophalen met deze clausule.

• U kunt deze clausule niet specificeren voor een weergave waarop een INSTEAD OF trigger is gedefinieerd.

error_logging_clause

De error_logging_clause heeft hetzelfde gedrag in een UPDATE -instructie als in een INSERT -instructie. Raadpleeg de INSERT verklaring error_logging_clause voor meer informatie.

Voorbeelden

Een tabel bijwerken: voorbeelden De volgende verklaring geeft nul commissies aan alle werknemers met de baan SH_CLERK:

UPDATE employees SET commission_pct = NULL WHERE job_id = "SH_CLERK";

De volgende verklaring promoot Douglas Grant tot manager van afdeling 20 met een verhoging van $ 1.000:

UPDATE employees SET job_id = "SA_MAN", salary = salary + 1000, department_id = 120 WHERE first_name||" "||last_name = "Douglas Grant"; 

De volgende verklaring verhoogt het salaris van een werknemer in de employees tabel in de remote database:

UPDATE employees@remote SET salary = salary*1.1 WHERE last_name = "Baer";

Het volgende voorbeeld toont de volgende syntactische constructies van de UPDATE statement:

• Beide vormen van de update_set_clause samen in één statement

• Een gecorreleerde subquery

• Een where_clause om de bijgewerkte rijen te beperken

De voorgaande UPDATE -instructie voert de volgende bewerkingen uit:

• Werkt alleen die werknemers bij die in Genève of München werken (locaties 2900 en 2700)

• Sets department_id voor deze werknemers naar de department_id die overeenkomt met Bombay (location_id 2100)

• Stelt het salaris van elke werknemer in op 1,1 keer het gemiddelde salaris van zijn afdeling.

• Stelt de commissie van elke werknemer in op 1,5 keer de gemiddelde commissie van zijn afdeling

Een partitie bijwerken: Voorbeeld In het volgende voorbeeld worden waarden bijgewerkt in een enkele partitie van de tabel sales:

UPDATE sales PARTITION (sales_q1_1999) s SET s.promo_id = 494 WHERE amount_sold > 1000;

Een objecttabel bijwerken: Voorbeeld Met de volgende instructie worden twee objecttabellen gemaakt, people_demo1 en people_demo2 , van het people_typ -object gemaakt in tabelverzamelingen: voorbeelden. Het voorbeeld laat zien hoe u een rij people_demo1 kunt bijwerken door een rij te selecteren uit people_demo2:

In het voorbeeld wordt de VALUE objectreferentiefunctie in zowel de SET clausule en de subquery.

Gecorreleerde update: voorbeeld Voor een voorbeeld dat gebruikt een gecorreleerde subquery om geneste tabelrijen bij te werken, raadpleeg “Tabelverzamelingen: voorbeelden”.

De clausule RETURNING gebruiken tijdens UPDATE: Voorbeeld Het volgende voorbeeld retourneert waarden uit de bijgewerkte rij en slaat het resultaat op in PL / SQL-variabelen bnd1, bnd2, bnd3:

De het volgende voorbeeld laat zien dat u een aggregatiefunctie met één set kunt specificeren in de uitdrukking van de terugkerende clausule:

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1;