Oracle Multi-Table Association UPDATE statement

1) The simplest form

SQL Code

-- confirmed that all customer_id less than 1000 in the Customers table are ' Beijing '

--within 1000 of the company to the country before the old customers of the city:)update customersset city_name=' Beijing 'where customer_id<          

2) Two tables (multiple tables) associated update--only in the WHERE clause of the connection

SQL Code

 -- this time the extracted data are VIP and include new, so by the way update the customer category update customers a -- use aliases set customer_type=01 ' --01 for vip,00 as normal " Span style= "color: #0000ff;" >where exists (select 1from tmp_cust_city b where B.customer_id=a.customer_id)    

3) Two tables (multiple tables) associated update--the modified value is calculated by another table

SQL Code

Update Customers A--Using aliasesSet City_name=(Select B.city_nameFrom Tmp_cust_city bwhere b.customer_id=A.CUSTOMER_ID)whereExists (Select1FromTmp_cust_city bwhere b.customer_id=A.CUSTOMER_ID)--Update more than 2 valuesUpdate Customers A--set (City_name, Customer_type) = (select  B.city_name,b.customer_typefrom tmp_cust_city bwhere b.customer_id=a.customer_id) where exists ( select 1 from tmp_cust_city b where B.customer_id=a.customer_id)    

-- 方法1. UPDATE 表2 SET    表2.C  =  ( SELECT B   FROM 表1   WHERE 表1.A = 表2.A) WHERE    EXISTS (  SELECT 1  FROM 表1   WHERE 表1.A = 表2.A)    -- 方法2 MERGE  INTO 表2  USING 表1 ON ( 表2.A = 表1.A )     -- 条件是 A 相同 WHEN MATCHED  THEN UPDATE SET 表2.C = 表1.B    -- 匹配的时候，更新Two

Oracle Random Read n data method in table:

1)  select *  from ( select *  from tablename  order by sys_guid())  where rownum < N;  2)  select *  from ( select *  from tablename  order by dbms_random.value)  where rownum< N;  3)  select *   from ( select *  from table_name sample(10)    order by trunc(dbms_random.value(0, 1000)))   where rownum < N;

Description
Sample (10) means retrieving 10% of the data in the table, and the sample value should be between [0.000001,99.999999], where Sys_guid () and dbms_random.value are intrinsic functions

Note:
When you make the 1 method, that is, when you use the Sys_guid () method, you sometimes get the same record, which is the same as the result set of the previous query (probably related to the operating system: Windows Normal, Linux exceptions, or perhaps because of the sys_guid () function itself, Pending further study)
Therefore, to ensure that the data read each time on different platforms is random, it is recommended to use both 2 and 3) scenarios where 2) are more commonly used. 3) The scheme narrows the scope of the query, in the Query large table, and to extract the data is not very few cases, will have a certain increase in query speed

Oracle Multi-Table Association UPDATE statement