Learn about Oracle update updates __oracle

This two days to the new colleagues to arrange a job, that is, to do an update cascade update, in the actual operation found a problem. For Oracle's newer syntax, most people, especially those who have studied SQL Server, have a lot of questions about Oracle updates when they use Oracle. Write down for future reference

Update a set a.col1 = (select B.col1 from b where b.col2 = A.col2)
where exists
(SELECT * from b where a.col2 = B.col2)

/**oracle is different from the use of update update for SQL Server, what does Oracle do with it?
Update a set a.col1=100 all rows in a specific column col1 to a specific value
Update a set a.col1=100 where a.col2<10 updates the value of the col1 column of the row that satisfies the col2 condition to a specific value
Update a set a.col1=a.col1+a.col2 where a.col2<10 a simple calculation update in the same table
Update a set a.col1= (select B.col1 from B where a.col2=b.col2)
where exists (SELECT * from B where a.col2=b.col2) cascade the update to update the a.col1 of rows that satisfy a.col2=b.col2 to the corresponding
The value of the b.col1. The Where condition can be removed when and only when a=b. This update can also be understood in this way:
Update a set a.col1= (select B.col1 from B where a.col2=b.col2) indicates that all rows in a are met A.col2=b.col2
To update, do not meet the conditions of the update, but can not find the corresponding value, only the null value, if the a.col1 is not allowed to be empty then reported inserted null value error.
So only the where condition can be added to a.col2<>b.col2 the data in a will survive (not be updated to empty). Oracle Insert, UPDATE, MERGE

ORACLE 2009-12-21 08:40:55 Read 68 Comments 0 Font Size: Large medium small Subscription/* Use course table as template, create a new table course_new, and include all records in Course table.

CREATE TABLE course_new as SELECT * from COURSE;

/* Clear all records in the course_new table * *
TRUNCATE TABLE course_new;

/* Add the following record to the Course_new table:
NO Course_name
A001 Oracle Database Management
A002 SQL Server Security Guide
A003 Hibernate full Raiders
A004. NET
*/
INSERT into Course_new (no,course_name)
SELECT ' A001 ', ' Oracle database Management ' from DUAL
UNION
SELECT ' A002 ', ' SQL Server Security Guide ' from DUAL
UNION
SELECT ' A003 ', ' Hibernate full Raiders ' from DUAL
UNION
SELECT ' A004 ', '. NET ' from DUAL;
COMMIT;

/* According to the No field in the course table, update the course table with course_new * *

UPDATE (SELECT/*+ BYPASS_UJVC */a.no,a.course_name,b.no as bno,b.course_name as Bname
From COURSE a,course_new B
WHERE a.no=b.no)
SET No=bno,course_name=bname
COMMIT;

/* Use the Insert/update and merge commands separately to update the records in the course table with course_new, or update if they exist insert*/
--implemented with merge as follows:

Insert into Course_new (no,course_name)--for ease of operation, insert a record in course_new first
SELECT ' A005 ', ' hcne network engineer ' from DUAL;
COMMIT;
MERGE into COURSE A
USING course_new B on (a.no=b.no)
When matched THEN
UPDATE SET A.course_name=b.course_name
When not matched THEN
INSERT (A.no,a.course_name)
VALUES (B.no,b.course_name);
COMMIT;

--using Insert/update to achieve the following

Insert into Course_new (no,course_name)--for ease of operation, insert a record in course_new
SELECT ' A006 ', ' CCNA network engineer ' from DUAL;
COMMIT;

--Update for fields with the same number with update
UPDATE COURSE A SET (no,course_name) =
(SELECT B.no,b.course_name
From Course_new B
WHERE a.no=b.no)
WHERE EXISTS
(SELECT 1 from Course_new B
WHERE a.no=b.no);
COMMIT;
--insert is not used in the original table to add
INSERT into COURSE
SELECT * from Course_new A
Where not EXISTS (SELECT 1 from COURSE B where a.no=b.no);
COMMIT;

/* Use a set of statements instead of the update operation for the entire table */
--first record the state of the COURSE table with Course_test to record the related fields after deleting the COURSE
CREATE table Course_test as SELECT * from   course  WHERE rownum<1;
INSERT/*+ APPEND/into Course_test
SELECT * from COURSE WHERE rownum<5;
COMMIT;
--Deletes the COURSE table
TRUNCATE tables COURSE
--Update COURSE table
INSERT/*+append*/into COURSE
SELECT a.no,a.course_name from course_new a,course_test B WHERE a.no=b. NO;
COMMIT;