Share multiple table join methods to update multiple different records at the same time

The following is a test example.
1. Create two temporary tables and input test data:
Copy codeThe Code is as follows:
Create table # temptest1
(
Id int,
Name1 varchar (50 ),
Age int
)
Create table # temptest2
(
Id int,
Name1 varchar (50 ),
Age int
)

The following table data is queried:

# Temptest1 # temptest2

 

2. Now we need to update the age in # temptest2 to the corresponding age in # temptest1.

In fact, the age of ID 1 in [Table 1] is changed to 19, and the age of ID 2 is changed to 20.

Of course, the requirements here are:Use only one SQL statement, and do not use Loops.

The result is 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

 

(Supplement) SQL Server 2008 Merge command syntax:

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

 

Is it an interesting SQL statement.

How to update multiple records with different values at a time
The title may not be clear. Suppose there are two tables:
Copy codeThe Code is 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 1
2 B 2
3 c 3
TestB:
Id eng chi any ....
========================
1 d 4
2 e 5
3 f 6
I want to update the ENG and CHI fields of the records in testB to testA and match them 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 );
Four rows have been updated.
Used time: 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)
Statistics
----------------------------------------------------------
0 recursive cballs
4 db block gets
23 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL * Net to client
856 bytes encoded ed 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 );
Four rows have been updated.
Used time: 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)
Statistics
----------------------------------------------------------
11 recursive cballs
1 db block gets
53 consistent gets
0 physical reads
588 redo size
840 bytes sent via SQL * Net to client
858 bytes encoded ed 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 ();

[Copy to clipboard] [-]

CODE:

SQL> update (select ta. B tab1, tb. B tbb from ta, tb where ta. a = tb. a) set tab1 = tbb;
Four rows have been updated.
Used time: 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". "")
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
8 recursive cballs
4 db block gets
17 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL * Net to client
827 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed