Delete, update, and join

Update:
Update a set approverid = NULL
From [sh_materialapplybuybill]
Left join [sh_materialapplybuybilldetail] B on A. ID = B. [materialapplybuybillid]
Where a. ID = 125 and @ indetailcount = 0
Delete:
Delete A from [sh_closingbalance] A left join [sh_storehouse] B on A. storehouseid = B. ID
Where B. Dimension mentid = '20140901'

========================================================== ====================================

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:

SQL code <id = player1255328313567 pluginspage = http://www.macromedia.com/go/getflashplayer src = http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 Height = 15 type = application/X-Shockwave-flash tplayername = "SWF" splayername = "SWF" mediawrapchecked = "true" flashvars = "Clipboard = Update % 20tb_user % 0 aset % 20 pass % 3d'' % 0 afrom % 20tb_user % 20usr % 0 ainner % 20 join % 20tb_address % 20 ADDR % 20on % 20usr. naddressfk % 20% 3D % 20addr. naddressid % 0 awhere % 20usr. id % 3d123 "Quality =" high "allowScriptAccess =" always ">

1. Update tb_user
2. Set pass =''
3. From tb_user USR
4. Inner join tb_address ADDR on USR. naddressfk = ADDR. naddressid
5. 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 T1Inner join T2 on t1.id = t2.tid

MySQL and access are written as follows:

SQL code <id = player1255328313600 pluginspage = http://www.macromedia.com/go/getflashplayer src = http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 Height = 15 type = application/X-Shockwave-flash tplayername = "SWF" splayername = "SWF" mediawrapchecked = "true" flashvars = "Clipboard = Update % 20mem_world % 20as % 20mw1% 20 inner % 20 join % 20mem_world % 20% 20as % 20% 20mw2% 20% 0aon % 20% 20mw1. parentid % 20% 3D % 20mw2. WID % 20% 0 aset % 20mw1. level % 20% 3D % 20mw2. level % 20% 20% 0 awhere % 20mw2. baseid % 20% 3D % 20107% 20% 20% 0 aand % 20% 20mw2. parentid % 20% 3D % 200% 20% 20% 0 aand % 20% 20mw2. size % 20% 3E % 201% 3B "Quality =" high "allowScriptAccess =" always ">

1. Update mem_world as mw1 inner join mem_world as mw2
2. On mw1.parentid = mw2.wid
3. Set mw1.level = mw2.level
4. Where mw2.base id = 107
5. And mw2.parentid = 0
6. And mw2.size> 1;

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:

SQL code <id = player1255328313633 pluginspage = http://www.macromedia.com/go/getflashplayer src = http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 Height = 15 type = application/X-Shockwave-flash tplayername = "SWF" splayername = "SWF" mediawrapchecked = "true" flashvars = "Clipboard = Delete % 20mwb % 20 from % 20% 20mem_world_building % 20as % 20mwb % 20 inner % 20 join % 20mem_world % 20as % 20 MW % 0aon % 20mwb. WID % 20% 3D % 20mw. WID % 0 awhere % 20mw. type % 20 between % 2011% 20and % 2015% 20% 0 aand % 20 baseid % 20% 3D % 20107% 20% 0 aand % 20mw. parentid % 20% 3C % 3E % 200% 20% 0 aand % 20% 20 MW. size % 20% 3E % 201% 3B "Quality =" high "allowScriptAccess =" always ">

1. Delete mwb from mem_world_building as mwb inner join mem_world As MW
2. On mwb. WID = mW. wid
3. Where mW. type between 11 and 15
4. And base id = 107
5. And mW. parentid <> 0
6. And mW. size> 1;

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:

 

SQL code <id = player1255328313684 pluginspage = http://www.macromedia.com/go/getflashplayer src = http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 Height = 15 type = application/X-Shockwave-flash tplayername = "SWF" splayername = "SWF" mediawrapchecked = "true" flashvars = "Clipboard = Delete % 20table1% 20 where % 20 khid % 20 exists % 20 (% 20 select % 20 khid % 20 from % 20table2% 20 where % 20 fwdwid % 3d8) "Quality =" high "allowScriptAccess =" always ">

1. Delete Table1 where khid exists (select khid from Table2 where fwdwid = 8)

Oracle Update (SQL) t set T. xx =

SQL code <id = player1255328313660 pluginspage = http://www.macromedia.com/go/getflashplayer src = http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 Height = 15 type = application/X-Shockwave-flash tplayername = "SWF" splayername = "SWF" mediawrapchecked = "true" flashvars = "Clipboard = Delete % 20table1% 20 where % 20 exists % 20 (% 20 select % 201% 20 from % 20table2% 20 where % 20and % 20table1. khid % 3dtable2. khid % 20and % 20 fwdwid % 3d8) % 3B % 0a "Quality =" high "allowScriptAccess =" always ">

1. Delete Table1 where exists (select 1 from Table2 where and table1.khid = table2.khid and fwdwid = 8 );

DELETE TABLE1 where exists ( select 1 from table2 where and table1.khid=table2.khid and FWDWID=8);