Use update inner join and delete inner join in SQL

Source: Internet
Author: User
Update  Update Xxx Set Xxx Where  As we all know about this writing, we found that update and delete support the update method of inner join, which is very useful for updating and deleting operations between tables. Column: SQLCode  Update  Tb_user  Set Pass =  ''        From  Tb_user USR  Inner   Join Tb_address ADDR On USR. naddressfk =  ADDR. naddressid  Where USR. ID =  123    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 T1 Inner   Join T2 On T1.id = T2.tidmysql, access statement: SQL code  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  ;  Update Mem_world As Mw1 Inner   Join Mem_worldAs 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 join. That is to say, after a table is connected, something similar to a temporary view is generated. Where filters data from this temporary view, first, you need to find out which of your so-called two conditions belong.  Delete  Delete  Statement is similar  Delete   From T1 From T1 Inner   Join T2 On T1.id =  T2.tid pay attention to the blue part. MySQL: SQL code 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  ;  Delete Mwb From Mem_world_building As MwbInner   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 the SQL code of Oracle:  Delete Table1 Where Khid Exists ( Select Khid From Table 2 Where Fwdwid =  8 ) SQL code  Delete Table1 Where   Exists ( Select   1   From Table 2 Where   And Table1.khid = Table2.khid And Fwdwid =  8  );  

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.