Use Inner join in SQL update Delete

Update

The update XXX set XXX Where statement is well known. Only then can we find that the update and delete statements support the inner join update method. This is very useful in Table Association for update and delete operations.

Column:

Update Tb_user
Set Pass = ''
From Tb_user USR
Inner Join Tb_address ADDR On USR. naddressfk = ADDR. naddressid
Where USR. ID = 123

 The update format is

Update T1 set t1.name = 'liu'From T1Inner join T2 on t1.id = t2.tid

MySQL and access are written as follows:

Update Mem_world As Mw1 Inner Join Mem_world As Mw2
On Mw1.parentid = Mw2.wid
Set Mw1. Level = Mw2. Level
Where Mw2.baseid = 107
And Mw2.parentid = 0
And Mw2.size > 1 ;

 

On is the filtering condition for table connections.
That is to say, after a table is connected, something similar to a temporary view will be generated.
Where filters data from this temporary view.
Therefore, you must first find out which of your so-called two conditions belong.

Delete

The delete statement is similar

Delete from T1From T1Inner join T2 on t1.id = t2.tid

Note:Blue.

MySQL:

Delete Mwb From Mem_world_building As Mwb Inner Join Mem_world As MW
On Mwb. wid = MW. wid
Where MW. Type Between 11 And 15
And Baseid = 107
And MW. parentid <> 0
And MW. Size > 1 ;

 

The following is Oracle: Delete Table1 Where Exists ( Select 1 From Table 2 Where And Table1.khid = Table2.khid And Fwdwid = 8 );

 

Delete Table1 Where Khid Exists ( Select Khid From Table 2 Where Fwdwid = 8 )

Original article: http://nodonkey.iteye.com/blog/456403