Source: http://hi.baidu.com/chaowei_yuwen/blog/item/e2078ecbabccc081c9176841.html
Update the data in another table from the data in one table:
Situation:
Because of Operation errors, the inventory part number does not match the goods, and the original data is updated with the newly provided Excel Data (many, and only the batch number lot_no and product prod_id.
Practice:
Creating a temporary table (using a temporary table to export data is a trick)
SQL:
Update inventory set prod_id =
(
Select prod_id from inventory_temp where inventory_temp.lot_no = inventory. lot_no
)
Where exists
(
Select lot_no from inventory_temp where inventory_temp.lot_no = inventory. lot_no
)
Experience:
Update another table with one table
The preceding statement can be abbreviated:
Update
Set a. Name = (select B. name from A. ID = B. ID)
Where exsits (select 1 from A. ID = B. ID );
If multiple fields are updated:
Update employees
Set department_id =
(Select department_id
From orders ments
Where location_id = '20140901 '),
(Salary, commission_pct) =
(Select 1.1 * AVG (salary), 1.5 * AVG (commission_pct)
From employees B
Where a. department_id = B. department_id)
Where department_id in
(Select department_id
From orders ments
Where location_id = 2900
Or location_id = 2700 );
-------------------------------------------
Another: http://blog.csdn.net/Bobwu/archive/2009/01/13/3768636.aspx
1.
Declare
Cursor T1 is select * From tablename;
Begin
For REC in T1 Loop
Update tablename t set T. Detail = Rec. jieshao where T. objectid = Rec. objid;
End loop;
End;
2.
Update student set (name, ID) =
(Select name, ID from (select student. rowid Rd, student1.name, student1.id from student1, student where student1.int _ id = student.int _ id) TMP
Where student. rowid = TMP. RD );
Commit;
3.
Update test_a set (A. Name, A. Age) =
(Select B. Name, B. Age from test_ B B where a. ID = B. ID) where exists
(Select * From test_ B c Where C. ID = A. ID)
4.
Update T_A set djrq =
(
Select djrq from T_ B where t_a.id = t_ B .id
Where rownum = 1
)
Where t_a.id in
(
Select ID from T_ B where jwh = 'xx cune'
)
5.
Update tbl1
Set (A. col1, A. col2) = (select B. col1, B. col2
From tbl2 B
Where a. Key = B. Key)
Where a. Key in (select key from tbl2)
Save backup