The following is a test example.
1. First create two temporary tables and input test data:
Copy Code code as follows:
CREATE TABLE #temptest1
(
ID int,
name1 varchar (50),
Age int
)
CREATE TABLE #temptest2
(
ID int,
name1 varchar (50),
Age int
)
The table data at this point in the query is:
#temptest1 #temptest2
2. It is now time to update the age in the #temptest2 to the corresponding #temptest1.
In fact, the age of ID 1 in table 1 is changed to 19, while the age of 2 is changed to 20.
Of course, the requirement here is to use only one sentence of SQL, not loop .
The results are as follows:
The implementation method is as follows:
Update T1
Set t1. Age = T2.age
From #temptest1 T1
Join #temptest2 T2
On t1.id = T2.id
Added SQL Server 2008 Merge command:
Merge into #temptest1 t1
Using (select Age,id from #temptest2) T2
On t1.id = T2.id
When matched then
Update Set t1.age = T2.age
Isn't that pretty funny sql.
How to update multiple records of different values at one one-time
The title may not be clear, assuming that there are two tables:
Copy Code code as follows:
CREATE TABLE Testa (
ID number,
Eng VARCHAR2 (3),
Chi Varchar2 (3)
)
CREATE TABLE Testb (
ID number,
Eng VARCHAR2 (3),
Chi Varchar2 (3),
Anythingother VARCHAR2 (1)
)
Existing records
Testa:
ID ENG CHI
===============
1 A A
2 B Two
3 C Three
TESTB:
ID ENG CHI any ....
=================
1 D Four
2 e Five
3 F Six
I want to update the Eng,chi fields of the records in Testb to the Testa and correspond with IDs.
CODE:
Sql> set Autot on
sql> Update TA set ta.b= (select Tb.b from TB where TA.A=TB.A) where exists (select 1 from TB where TA.A=TB.A);
4 rows have been updated.
Time used: 00:00:00.01
Execution plan
----------------------------------------------------------
Plan Hash value:1137212925
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30) | 00:00:01 |
| 1 | UPDATE | TA | | | |
|* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20) | 00:00:01 |
| 3 | TABLE ACCESS Full | TA | 5 | 100 | 2 (0) | 00:00:01 |
| 4 | VIEW | Vw_sq_1 | 4 | 52 | 2 (0) | 00:00:01 |
| 5 | TABLE ACCESS full| TB | 4 | 52 | 2 (0) | 00:00:01 |
|* 6 | TABLE ACCESS Full | TB | 1 | 26 | 2 (0) | 00:00:01 |
--------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("TA".) A "=" item_1 ")
6-filter ("TB".) A "=:B1)
Note
-----
-Dynamic sampling used for this statement (level=2)
Statistical information
----------------------------------------------------------
0 Recursive calls
4 db block gets
Consistent gets
0 physical Reads
1004 Redo Size
840 Bytes sent via sql*net to client
856 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
4 rows processed
sql> Update TA set ta.b= (select Tb.b from TB where TA.A=TB.A) where ta.a= (select TB.A from TB where TA.A=TB.A);
4 rows have been updated.
Time used: 00:00:00.00
Execution plan
----------------------------------------------------------
Plan Hash value:3571861550
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15) | 00:00:01 |
| 1 | UPDATE | TA | | | |
|* 2 | FILTER | | |
| 3 | TABLE ACCESS full| TA | 5 | 100 | 2 (0) | 00:00:01 |
|* 4 | TABLE ACCESS full| TB | 1 | 13 | 2 (0) | 00:00:01 |
|* 5 | TABLE ACCESS Full | TB | 1 | 26 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("TA".) A "= (SELECT" TB ".) A "from" TB "TB" WHERE
"TB". " A "=:B1)"
4-filter ("TB".) A "=:B1)
5-filter ("TB".) A "=:B1)
Note
-----
-Dynamic sampling used for this statement (level=2)
Statistical information
----------------------------------------------------------
One recursive calls
1 db block gets
Consistent gets
0 physical Reads
588 Redo Size
840 Bytes sent via sql*net to client
858 Bytes received via sql*net from client
3 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
4 rows processed
If create unique index TB_A_UIDX on TB (a);
[Copy to Clipboard] [ - ]
CODE:
sql> Update (select Ta.b tab1, tb.b TBB from TA,TB where TA.A=TB.A) set TAB1=TBB;
4 rows have been updated.
Time used: 00:00:00.01
Execution plan
----------------------------------------------------------
Plan Hash value:1761655026
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20) | 00:00:01 |
| 1 | UPDATE | TA | | | |
|* 2 | HASH JOIN | | 4 | 184 | 5 (20) | 00:00:01 |
| 3 | TABLE ACCESS full| TB | 4 | 104 | 2 (0) | 00:00:01 |
| 4 | TABLE ACCESS full| TA | 5 | 100 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("TA".) "A" = "TB". A ")
Note
-----
-Dynamic sampling used for this statement (level=2)
Statistical information
----------------------------------------------------------
8 Recursive calls
4 db block gets
Consistent gets
0 physical Reads
1004 Redo Size
840 Bytes sent via sql*net to client
827 Bytes received via sql*net from client
3 sql*net roundtrips To/from Client
3 Sorts (memory)
0 Sorts (disk)
4 rows processed
