利用带关联子查询Update语句更新数据的方法
<p>Update是T-sql中再简单不过的语句了，update table set column=expression ，我们都会用到。但update的用法不仅于此，真正在开发的时候，灵活恰当地使用update可以达到事半功倍的效果。</p>
<p>
假定有表Table1（a,b,c）和Table2（a,c)，现在Table1中有些记录字段c为null，要根据字段a在Table2中查找，取出字段a相等的字段c的值来更新Table1。一种常规的思路，通过游标遍历Table1中字段c为null的所有记录，在循环体内查找Table2并进行更新，即用游标Cursor的形式。测试sql语句如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_653115">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
<div class="line number45 index44 alt2">
45</div>
<div class="line number46 index45 alt1">
46</div>
<div class="line number47 index46 alt2">
47</div>
<div class="line number48 index47 alt1">
48</div>
<div class="line number49 index48 alt2">
49</div>
<div class="line number50 index49 alt1">
50</div>
<div class="line number51 index50 alt2">
51</div>
<div class="line number52 index51 alt1">
52</div>
<div class="line number53 index52 alt2">
53</div>
<div class="line number54 index53 alt1">
54</div>
<div class="line number55 index54 alt2">
55</div>
<div class="line number56 index55 alt1">
56</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql comments">--1.创建测试表</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">create</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">b </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">c </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">CONSTRAINT</code> <code class="sql plain"> </code><code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code> <code class="sql plain">CLUSTERED</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">ASC</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">) </code><code class="sql keyword">ON</code> <code class="sql plain">[</code><code class="sql keyword">PRIMARY</code><code class="sql plain">]</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">create</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table2</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql plain">c </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql keyword">CONSTRAINT</code> <code class="sql plain"> </code><code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code> <code class="sql plain">CLUSTERED</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">ASC</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql plain">) </code><code class="sql keyword">ON</code> <code class="sql plain">[</code><code class="sql keyword">PRIMARY</code><code class="sql plain">]</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code><code class="sql comments">--2.创建测试数据</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'赵'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">)</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'钱'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql string">'100'</code><code class="sql plain">)</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'孙'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql string">'80'</code><code class="sql plain">)</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'李'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">)</code>
</div>
<div class="line number28 index27 alt1">
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'赵'</code><code class="sql plain">,</code><code class="sql string">'90'</code><code class="sql plain">)</code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'钱'</code><code class="sql plain">,</code><code class="sql string">'100'</code><code class="sql plain">)</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'孙'</code><code class="sql plain">,</code><code class="sql string">'80'</code><code class="sql plain">)</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'李'</code><code class="sql plain">,</code><code class="sql string">'95'</code><code class="sql plain">)</code>
</div>
<div class="line number33 index32 alt2">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">Table1</code>
</div>
<div class="line number35 index34 alt2">
</div>
<div class="line number36 index35 alt1">
<code class="sql spaces"> </code><code class="sql comments">--3.通过游标方式更新</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">@</code><code class="sql keyword">name</code> <code class="sql keyword">varchar</code><code class="sql plain">(10)</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">@score </code><code class="sql keyword">varchar</code><code class="sql plain">(10)</code>
</div>
<div class="line number39 index38 alt2">
<code class="sql spaces"> </code><code class="sql keyword">declare</code> <code class="sql plain">mycursor </code><code class="sql keyword">cursor</code> <code class="sql keyword">for</code> <code class="sql keyword">select</code> <code class="sql plain">a </code><code class="sql keyword">from</code> <code class="sql plain">Table1 </code><code class="sql keyword">where</code> <code class="sql plain">c </code><code class="sql keyword">is</code> <code class="sql color1">null</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql spaces"> </code><code class="sql keyword">open</code> <code class="sql plain">mycursor</code>
</div>
<div class="line number41 index40 alt2">
<code class="sql spaces"> </code><code class="sql keyword">fetch</code> <code class="sql keyword">next</code> <code class="sql keyword">from</code> <code class="sql plain">mycursor </code><code class="sql keyword">into</code> <code class="sql plain">@</code><code class="sql keyword">name</code>
</div>
<div class="line number42 index41 alt1">
<code class="sql spaces"> </code><code class="sql plain">while(@@fetch_status = 0)</code>
</div>
<div class="line number43 index42 alt2">
<code class="sql spaces"> </code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number44 index43 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">@score=c </code><code class="sql keyword">from</code> <code class="sql plain">Table2 </code><code class="sql keyword">where</code> <code class="sql plain">a=@</code><code class="sql keyword">name</code>
</div>
<div class="line number45 index44 alt2">
<code class="sql spaces"> </code><code class="sql keyword">update</code> <code class="sql plain">Table1 </code><code class="sql keyword">set</code> <code class="sql plain">c = @score </code><code class="sql keyword">where</code> <code class="sql plain">a = @</code><code class="sql keyword">name</code>
</div>
<div class="line number46 index45 alt1">
<code class="sql spaces"> </code><code class="sql keyword">fetch</code> <code class="sql keyword">next</code> <code class="sql keyword">from</code> <code class="sql plain">mycursor </code><code class="sql keyword">into</code> <code class="sql plain">@</code><code class="sql keyword">name</code>
</div>
<div class="line number47 index46 alt2">
<code class="sql spaces"> </code><code class="sql keyword">END</code>
</div>
<div class="line number48 index47 alt1">
<code class="sql spaces"> </code><code class="sql keyword">close</code> <code class="sql plain">mycursor</code>
</div>
<div class="line number49 index48 alt2">
<code class="sql spaces"> </code><code class="sql keyword">deallocate</code> <code class="sql plain">mycursor</code>
</div>
<div class="line number50 index49 alt1">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number51 index50 alt2">
<code class="sql spaces"> </code><code class="sql comments">--4.显示更新后的结果</code>
</div>
<div class="line number52 index51 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">Table1</code>
</div>
<div class="line number53 index52 alt2">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number54 index53 alt1">
<code class="sql spaces"> </code><code class="sql comments">--5.删除测试表</code>
</div>
<div class="line number55 index54 alt2">
<code class="sql spaces"> </code><code class="sql keyword">drop</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table1</code>
</div>
<div class="line number56 index55 alt1">
<code class="sql spaces"> </code><code class="sql keyword">drop</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table2</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
虽然用游标可以实现，但代码看起来很复杂，其实用Update根据子关联来更新只要一条语句就可以搞定了，测试代码如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_277906">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
<div class="line number45 index44 alt2">
45</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql comments">--1.创建测试表</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql keyword">create</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">b </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">c </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql keyword">CONSTRAINT</code> <code class="sql plain"> </code><code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code> <code class="sql plain">CLUSTERED</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">ASC</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">) </code><code class="sql keyword">ON</code> <code class="sql plain">[</code><code class="sql keyword">PRIMARY</code><code class="sql plain">]</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql keyword">create</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table2</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql plain">c </code><code class="sql keyword">varchar</code><code class="sql plain">(10),</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql keyword">CONSTRAINT</code> <code class="sql plain"> </code><code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code> <code class="sql plain">CLUSTERED</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql plain">a </code><code class="sql keyword">ASC</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql plain">)</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql plain">) </code><code class="sql keyword">ON</code> <code class="sql plain">[</code><code class="sql keyword">PRIMARY</code><code class="sql plain">]</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code><code class="sql comments">--2.创建测试数据</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'赵'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">)</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'钱'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql string">'100'</code><code class="sql plain">)</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'孙'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql string">'80'</code><code class="sql plain">)</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table1 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'李'</code><code class="sql plain">,</code><code class="sql string">'asds'</code><code class="sql plain">,</code><code class="sql color1">null</code><code class="sql plain">)</code>
</div>
<div class="line number28 index27 alt1">
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'赵'</code><code class="sql plain">,</code><code class="sql string">'90'</code><code class="sql plain">)</code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'钱'</code><code class="sql plain">,</code><code class="sql string">'100'</code><code class="sql plain">)</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'孙'</code><code class="sql plain">,</code><code class="sql string">'80'</code><code class="sql plain">)</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code><code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">Table2 </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'李'</code><code class="sql plain">,</code><code class="sql string">'95'</code><code class="sql plain">)</code>
</div>
<div class="line number33 index32 alt2">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">Table1</code>
</div>
<div class="line number35 index34 alt2">
</div>
<div class="line number36 index35 alt1">
<code class="sql spaces"> </code><code class="sql comments">--3.通过Update方式更新</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code><code class="sql keyword">Update</code> <code class="sql plain">Table1 </code><code class="sql keyword">set</code> <code class="sql plain">c = (</code><code class="sql keyword">select</code> <code class="sql plain">c </code><code class="sql keyword">from</code> <code class="sql plain">Table2 </code><code class="sql keyword">where</code> <code class="sql plain">a = Table1.a) </code><code class="sql keyword">where</code> <code class="sql plain">c </code><code class="sql keyword">is</code> <code class="sql color1">null</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number39 index38 alt2">
</div>
<div class="line number40 index39 alt1">
<code class="sql spaces"> </code><code class="sql comments">--4.显示更新后的结果</code>
</div>
<div class="line number41 index40 alt2">
<code class="sql spaces"> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">Table1</code>
</div>
<div class="line number42 index41 alt1">
<code class="sql spaces"> </code><code class="sql plain">GO</code>
</div>
<div class="line number43 index42 alt2">
<code class="sql spaces"> </code><code class="sql comments">--5.删除测试表</code>
</div>
<div class="line number44 index43 alt1">
<code class="sql spaces"> </code><code class="sql keyword">drop</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table1</code>
</div>
<div class="line number45 index44 alt2">
<code class="sql spaces"> </code><code class="sql keyword">drop</code> <code class="sql keyword">TABLE</code> <code class="sql plain">Table2</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
参考资料：也许是被忽略的update语句，update 子查询</p>
頁:
[1]