Oracle sql updating multiple rows
But it often cannot, or it is too difficult to make it do so, so resorting to SQL is the way to go.
SET SERVEROUTPUT ON DROP TABLE post; CREATE TABLE post ( id INT NOT NULL PRIMARY KEY, text VARCHAR2(1000) NOT NULL, archived NUMBER(1) NOT NULL CHECK (archived IN (0, 1)), creation_date DATE NOT NULL ); CREATE INDEX post_creation_date_i ON post (creation_date); ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE; CREATE TABLE results ( run NUMBER(2), stmt NUMBER(2), elapsed NUMBER ); DECLARE v_ts TIMESTAMP WITH TIME ZONE; PROCEDURE reset_post IS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE post'; INSERT INTO post SELECT level AS id, lpad('a', 1000, 'a') AS text, 0 AS archived, DATE '2017-01-01' (level / 100) AS creation_date FROM dual CONNECT BY level import
But you can also update whole sets of records at once, and in very powerful ways. For example, you can delete rows in one table depending on whether or not they exist in another table.
SQL even has a way, a relatively new addition to the standard, by which you can insert, update, and delete all at once. For example, you want to insert a new record into the DEPT table.
The result is (same time unit): Run 0, Statement 1: PT4.546S Run 0, Statement 2: PT3.52S Run 0, Statement 3: PT0.144S Run 0, Statement 4: PT0.028S Run 1, Statement 1: PT3.712S Run 1, Statement 2: PT3.185S Run 1, Statement 3: PT0.138S Run 1, Statement 4: PT0.025S Run 2, Statement 1: PT3.481S Run 2, Statement 2: PT3.007S Run 2, Statement 3: PT0.122S Run 2, Statement 4: PT0.026S Run 3, Statement 1: PT3.518S Run 3, Statement 2: PT3.077S Run 3, Statement 3: PT0.113S Run 3, Statement 4: PT0.027S Run 4, Statement 1: PT3.54S Run 4, Statement 2: PT2.94S Run 4, Statement 3: PT0.123S Run 4, Statement 4: PT0.03S The difference between Statement 1 and 4 is a factor of 100x !! Again (by far): Statement 4, running the bulk update In fact, the time is not too far away from the time taken by PL/SQL.
With larger data sets being updated, the two results will converge. While we're running individual row-by-row updates, we're sending all the update statements in one batch to the SQL engine.
Let's say we have a simple table for our blog posts (using Oracle syntax, but the effect is the same on all databases): CREATE TABLE post ( id INT NOT NULL PRIMARY KEY, text VARCHAR2(1000) NOT NULL, archived NUMBER(1) NOT NULL CHECK (archived IN (0, 1)), creation_date DATE NOT NULL ); CREATE INDEX post_creation_date_i ON post (creation_date); statement is probably better than looping over each individual row and updating each individual row explicitly. Showing the time it takes for each statement type to complete, each time updating 3649 / 10000 rows.
The winner is: Statement 1, running a bulk update It looks like this: We'd get far worse results for larger tables!
You want to copy rows from one table to another by using a query.All brands support use of the DEFAULT keyword as a way of explicitly specifying the default value for a column.