mysql - 更新查询不起作用 7-8% 的记录
问题描述
我想这并不容易。我有一个工作正常的简单更新查询，问题是 7-8% 的记录没有更新。似乎 LEFT JOIN 不能总是匹配 ON 子句。
我检查了不匹配的字段，看看是否有一些空格，但没有。我还检查了 LEFT 表中的重复项，这里也没有。当然，我还检查了两个表中是否存在记录。
我在 Windows 10 下使用 MySQL 8.0
这是我的代码：
UPDATE `61_LoadUnit`
LEFT JOIN `statistics`.`tbl_g08t1`
ON `61_LoadUnit`.`StUnit` = `statistics`.`tbl_g08t1`.`ecarrno`
SET `61_LoadUnit`.`carrno` = `statistics`.`tbl_g08t1`.`carrno`
WHERE `61_LoadUnit`.`carrno` IN (NULL, 0);
的一些数据StUnit=29318284。抱歉，我不知道如何粘贴表格。
61_loadunit
<html>
<head>
<title>61_loadunit</title>
<meta name="GENERATOR" content="HeidiSQL 10.2.0.5599">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
thead tr {background-color: ActiveCaption; color: CaptionText;}
th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
table, td {border: 1px solid silver;}
table {border-collapse: collapse;}
thead .col0 {width: 64px;}
thead .col1 {width: 89px;}
thead .col2 {width: 83px;}
thead .col3 {width: 90px;}
thead .col4 {width: 52px;}
thead .col5 {width: 51px;}
thead .col6 {width: 35px;}
thead .col7 {width: 35px;}
thead .col8 {width: 89px;}
thead .col9 {width: 95px;}
thead .col10 {width: 83px;}
.col10 {text-align: right;}
thead .col11 {width: 88px;}
.col11 {text-align: right;}
thead .col12 {width: 157px;}
thead .col13 {width: 67px;}
thead .col14 {width: 71px;}
thead .col15 {width: 70px;}
.col15 {text-align: right;}
thead .col16 {width: 87px;}
.col16 {text-align: right;}
thead .col17 {width: 147px;}
thead .col18 {width: 83px;}
</style>
</head>
<body>
<table caption="61_loadunit (3 rows)">
<thead>
<tr>
<th class="col0">Division</th>
<th class="col1">Confirmation</th>
<th class="col2">RefID</th>
<th class="col3">WCS_LoadId</th>
<th class="col4">MHA</th>
<th class="col5">Rack</th>
<th class="col6">X</th>
<th class="col7">Y</th>
<th class="col8">StUnit</th>
<th class="col9">Weight_LW61</th>
<th class="col10">carrno</th>
<th class="col11">Shortl62</th>
<th class="col12">Partno</th>
<th class="col13">PartRev</th>
<th class="col14">DivCode</th>
<th class="col15">Quantity</th>
<th class="col16">partwght</th>
<th class="col17">CreationDateTime</th>
<th class="col18">RunDate</th>
</tr>
</thead>
<tbody>
<tr>
<td class="col0">WCS1</td>
<td class="col1">0</td>
<td class="col2">10762181</td>
<td class="col3">10762181</td>
<td class="col4">ID1</td>
<td class="col5"></td>
<td class="col6"></td>
<td class="col7"></td>
<td class="col8">AKS22282</td>
<td class="col9">482.00</td>
<td class="col10">32614650</td>
<td class="col11">166540</td>
<td class="col12">RB-12.7/G16VX310A </td>
<td class="col13"> </td>
<td class="col14">AKS </td>
<td class="col15">54000</td>
<td class="col16">0.008454</td>
<td class="col17">2019-08-29T14:52:09Z</td>
<td class="col18">20190830</td>
</tr>
<tr>
<td class="col0">WCS1</td>
<td class="col1">0</td>
<td class="col2">10762178</td>
<td class="col3">10762178</td>
<td class="col4">ID1</td>
<td class="col5"></td>
<td class="col6"></td>
<td class="col7"></td>
<td class="col8">799197533</td>
<td class="col9">126.20</td>
<td class="col10">32577072</td>
<td class="col11">223198</td>
<td class="col12">PER.UCFX12A-A </td>
<td class="col13">33 47 </td>
<td class="col14">479PBE</td>
<td class="col15">15</td>
<td class="col16">5.8</td>
<td class="col17">2019-08-29T14:51:49Z</td>
<td class="col18">20190830</td>
</tr>
<tr>
<td class="col0">WCS1</td>
<td class="col1">0</td>
<td class="col2">10762172</td>
<td class="col3">10762172</td>
<td class="col4">ID1</td>
<td class="col5"></td>
<td class="col6"></td>
<td class="col7"></td>
<td class="col8">29318284</td>
<td class="col9">223.80</td>
<td class="col10"></td>
<td class="col11"></td>
<td class="col12"></td>
<td class="col13"></td>
<td class="col14"></td>
<td class="col15"></td>
<td class="col16"></td>
<td class="col17">2019-08-29T14:51:29Z</td>
<td class="col18">20190830</td>
</tr>
</tbody>
</table>
</body>
</html>
tbl_g08t1
<html>
<head>
<title>tbl_g08t1</title>
<meta name="GENERATOR" content="HeidiSQL 10.2.0.5599">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
thead tr {background-color: ActiveCaption; color: CaptionText;}
th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
table, td {border: 1px solid silver;}
table {border-collapse: collapse;}
thead .col0 {width: 87px;}
.col0 {text-align: right;}
thead .col1 {width: 91px;}
.col1 {text-align: right;}
thead .col2 {width: 77px;}
.col2 {text-align: right;}
thead .col3 {width: 69px;}
.col3 {text-align: right;}
thead .col4 {width: 66px;}
.col4 {text-align: right;}
thead .col5 {width: 74px;}
.col5 {text-align: right;}
thead .col6 {width: 50px;}
.col6 {text-align: right;}
thead .col7 {width: 71px;}
.col7 {text-align: right;}
thead .col8 {width: 62px;}
.col8 {text-align: right;}
thead .col9 {width: 46px;}
.col9 {text-align: right;}
thead .col10 {width: 62px;}
.col10 {text-align: right;}
thead .col11 {width: 64px;}
thead .col12 {width: 54px;}
thead .col13 {width: 65px;}
thead .col14 {width: 65px;}
thead .col15 {width: 56px;}
thead .col16 {width: 63px;}
thead .col17 {width: 70px;}
.col17 {text-align: right;}
thead .col18 {width: 63px;}
.col18 {text-align: right;}
thead .col19 {width: 62px;}
.col19 {text-align: right;}
thead .col20 {width: 69px;}
.col20 {text-align: right;}
thead .col21 {width: 87px;}
.col21 {text-align: right;}
thead .col22 {width: 69px;}
.col22 {text-align: right;}
thead .col23 {width: 93px;}
.col23 {text-align: right;}
thead .col24 {width: 138px;}
thead .col25 {width: 138px;}
thead .col26 {width: 138px;}
thead .col27 {width: 110px;}
thead .col28 {width: 135px;}
thead .col29 {width: 58px;}
.col29 {text-align: right;}
thead .col30 {width: 62px;}
.col30 {text-align: right;}
thead .col31 {width: 66px;}
.col31 {text-align: right;}
thead .col32 {width: 56px;}
.col32 {text-align: right;}
thead .col33 {width: 54px;}
.col33 {text-align: right;}
thead .col34 {width: 58px;}
.col34 {text-align: right;}
thead .col35 {width: 92px;}
thead .col36 {width: 92px;}
thead .col37 {width: 92px;}
thead .col38 {width: 69px;}
.col38 {text-align: right;}
thead .col39 {width: 108px;}
thead .col40 {width: 60px;}
.col40 {text-align: right;}
thead .col41 {width: 65px;}
.col41 {text-align: right;}
thead .col42 {width: 71px;}
.col42 {text-align: right;}
thead .col43 {width: 71px;}
.col43 {text-align: right;}
thead .col44 {width: 73px;}
.col44 {text-align: right;}
thead .col45 {width: 74px;}
.col45 {text-align: right;}
thead .col46 {width: 54px;}
.col46 {text-align: right;}
thead .col47 {width: 94px;}
thead .col48 {width: 193px;}
thead .col49 {width: 74px;}
.col49 {text-align: right;}
thead .col50 {width: 83px;}
.col50 {text-align: right;}
thead .col51 {width: 61px;}
.col51 {text-align: right;}
thead .col52 {width: 71px;}
.col52 {text-align: right;}
thead .col53 {width: 71px;}
.col53 {text-align: right;}
thead .col54 {width: 138px;}
thead .col55 {width: 138px;}
thead .col56 {width: 63px;}
thead .col57 {width: 76px;}
.col57 {text-align: right;}
thead .col58 {width: 70px;}
.col58 {text-align: right;}
thead .col59 {width: 70px;}
.col59 {text-align: right;}
thead .col60 {width: 69px;}
.col60 {text-align: right;}
thead .col61 {width: 66px;}
.col61 {text-align: right;}
thead .col62 {width: 76px;}
.col62 {text-align: right;}
thead .col63 {width: 71px;}
.col63 {text-align: right;}
thead .col64 {width: 67px;}
.col64 {text-align: right;}
thead .col65 {width: 67px;}
.col65 {text-align: right;}
thead .col66 {width: 75px;}
.col66 {text-align: right;}
thead .col67 {width: 140px;}
thead .col68 {width: 57px;}
.col68 {text-align: right;}
thead .col69 {width: 71px;}
.col69 {text-align: right;}
thead .col70 {width: 69px;}
.col70 {text-align: right;}
thead .col71 {width: 79px;}
thead .col72 {width: 48px;}
thead .col73 {width: 54px;}
thead .col74 {width: 83px;}
thead .col75 {width: 67px;}
thead .col76 {width: 67px;}
thead .col77 {width: 54px;}
thead .col78 {width: 54px;}
thead .col79 {width: 85px;}
thead .col80 {width: 77px;}
thead .col81 {width: 59px;}
thead .col82 {width: 76px;}
thead .col83 {width: 67px;}
thead .col84 {width: 67px;}
</style>
</head>
<body>
<table caption="tbl_g08t1 (3 rows)">
<thead>
<tr>
<th class="col0">shortl62</th>
<th class="col1">carrno</th>
<th class="col2">shortg04</th>
<th class="col3">carrtype</th>
<th class="col4">carrstat</th>
<th class="col5">approved</th>
<th class="col6">avail</th>
<th class="col7">reserved</th>
<th class="col8">carrctrl</th>
<th class="col9">lock</th>
<th class="col10">labeled</th>
<th class="col11">mha</th>
<th class="col12">rack</th>
<th class="col13">horcoor</th>
<th class="col14">vercoor</th>
<th class="col15">ldct</th>
<th class="col16">loctype</th>
<th class="col17">stkquant</th>
<th class="col18">stkzero</th>
<th class="col19">delamo</th>
<th class="col20">amooncr</th>
<th class="col21">carrwght</th>
<th class="col22">ldctwght</th>
<th class="col23">carrvol</th>
<th class="col24">regdate</th>
<th class="col25">statdate</th>
<th class="col26">stkdate</th>
<th class="col27">freetxt1</th>
<th class="col28">freetxt2</th>
<th class="col29">minval</th>
<th class="col30">maxval</th>
<th class="col31">freerow</th>
<th class="col32">depth</th>
<th class="col33">width</th>
<th class="col34">height</th>
<th class="col35">bbdate</th>
<th class="col36">ltdate</th>
<th class="col37">lsdate</th>
<th class="col38">lockcode</th>
<th class="col39">allomark</th>
<th class="col40">refg08</th>
<th class="col41">ocarrno</th>
<th class="col42">shortg00</th>
<th class="col43">shortg02</th>
<th class="col44">marktype</th>
<th class="col45">checksum</th>
<th class="col46">origin</th>
<th class="col47">ecarrno</th>
<th class="col48">g08fifo</th>
<th class="col49">g08huqty</th>
<th class="col50">shorto42</th>
<th class="col51">icarrno</th>
<th class="col52">shortg07</th>
<th class="col53">shortg37</th>
<th class="col54">g08pdate</th>
<th class="col55">g08stkch</th>
<th class="col56">origldct</th>
<th class="col57">g08uwrap</th>
<th class="col58">g08retur</th>
<th class="col59">g08scale</th>
<th class="col60">g08stort</th>
<th class="col61">g08tqty</th>
<th class="col62">g08badpu</th>
<th class="col63">g08stqty</th>
<th class="col64">firecode</th>
<th class="col65">shortl66</th>
<th class="col66">g08bcode</th>
<th class="col67">ldctid</th>
<th class="col68">ldctori</th>
<th class="col69">g08crosc</th>
<th class="col70">shortf04</th>
<th class="col71">rundate</th>
<th class="col72">txt8</th>
<th class="col73">txt50</th>
<th class="col74">txt71</th>
<th class="col75">txt72</th>
<th class="col76">txt73</th>
<th class="col77">txt74</th>
<th class="col78">txt75</th>
<th class="col79">txt76</th>
<th class="col80">txt97</th>
<th class="col81">txt98</th>
<th class="col82">txt132</th>
<th class="col83">txt137</th>
<th class="col84">txt155</th>
</tr>
</thead>
<tbody>
<tr>
<td class="col0">165550</td>
<td class="col1">33948712</td>
<td class="col2">2249873</td>
<td class="col3">0</td>
<td class="col4">6</td>
<td class="col5">1</td>
<td class="col6">1</td>
<td class="col7">0</td>
<td class="col8">0</td>
<td class="col9">0</td>
<td class="col10">0</td>
<td class="col11">B </td>
<td class="col12">011</td>
<td class="col13">009</td>
<td class="col14">008</td>
<td class="col15">P01</td>
<td class="col16"> </td>
<td class="col17">6720</td>
<td class="col18">0</td>
<td class="col19">134400</td>
<td class="col20">73600</td>
<td class="col21">110.4</td>
<td class="col22">0</td>
<td class="col23">68.448</td>
<td class="col24">2016-04-28 22:53:09</td>
<td class="col25">2017-11-24 15:08:55</td>
<td class="col26">2017-11-24 15:08:55</td>
<td class="col27"> </td>
<td class="col28">L79CREXP </td>
<td class="col29">0</td>
<td class="col30">0</td>
<td class="col31">7</td>
<td class="col32">0</td>
<td class="col33">0</td>
<td class="col34">0</td>
<td class="col35"> </td>
<td class="col36"> </td>
<td class="col37"> </td>
<td class="col38">0</td>
<td class="col39"> </td>
<td class="col40">0</td>
<td class="col41">0</td>
<td class="col42">0</td>
<td class="col43">0</td>
<td class="col44">0</td>
<td class="col45">0</td>
<td class="col46">0</td>
<td class="col47">J14122156</td>
<td class="col48">0000165550.16042822:53 </td>
<td class="col49">0</td>
<td class="col50">0</td>
<td class="col51">0</td>
<td class="col52">0</td>
<td class="col53">0</td>
<td class="col54">2016-04-28 22:53:09</td>
<td class="col55">2016-09-27 01:40:37</td>
<td class="col56"> </td>
<td class="col57">0</td>
<td class="col58">0</td>
<td class="col59">0</td>
<td class="col60">0</td>
<td class="col61">0</td>
<td class="col62">0</td>
<td class="col63">0</td>
<td class="col64">0</td>
<td class="col65">0</td>
<td class="col66">0</td>
<td class="col67"> </td>
<td class="col68">0</td>
<td class="col69">0</td>
<td class="col70">0</td>
<td class="col71">19-08-29</td>
<td class="col72"></td>
<td class="col73"></td>
<td class="col74">20160427</td>
<td class="col75">20160321</td>
<td class="col76"></td>
<td class="col77">J-SLS201609S</td>
<td class="col78">03</td>
<td class="col79">J14122156</td>
<td class="col80"></td>
<td class="col81"></td>
<td class="col82">JGBR</td>
<td class="col83"></td>
<td class="col84"></td>
</tr>
<tr>
<td class="col0">165551</td>
<td class="col1">378320</td>
<td class="col2">2401256</td>
<td class="col3">0</td>
<td class="col4">6</td>
<td class="col5">1</td>
<td class="col6">1</td>
<td class="col7">0</td>
<td class="col8">0</td>
<td class="col9">0</td>
<td class="col10">0</td>
<td class="col11">A </td>
<td class="col12">011</td>
<td class="col13">054</td>
<td class="col14">011</td>
<td class="col15">P01</td>
<td class="col16"> </td>
<td class="col17">4920</td>
<td class="col18">0</td>
<td class="col19">98400</td>
<td class="col20">98400</td>
<td class="col21">223.8</td>
<td class="col22">0</td>
<td class="col23">65.928</td>
<td class="col24">2019-08-29 11:27:59</td>
<td class="col25">2019-08-29 14:55:29</td>
<td class="col26">2019-08-29 11:27:59</td>
<td class="col27"> </td>
<td class="col28">TI39405 </td>
<td class="col29">0</td>
<td class="col30">0</td>
<td class="col31">5</td>
<td class="col32">0</td>
<td class="col33">0</td>
<td class="col34">0</td>
<td class="col35"> </td>
<td class="col36"> </td>
<td class="col37"> </td>
<td class="col38">0</td>
<td class="col39"> </td>
<td class="col40">0</td>
<td class="col41">0</td>
<td class="col42">0</td>
<td class="col43">0</td>
<td class="col44">0</td>
<td class="col45">0</td>
<td class="col46">0</td>
<td class="col47">29318284</td>
<td class="col48">0000165551.19082911:27 </td>
<td class="col49">0</td>
<td class="col50">0</td>
<td class="col51">0</td>
<td class="col52">0</td>
<td class="col53">0</td>
<td class="col54">2019-08-29 11:27:59</td>
<td class="col55">2019-08-29 11:27:59</td>
<td class="col56"> </td>
<td class="col57">0</td>
<td class="col58">0</td>
<td class="col59">0</td>
<td class="col60">3</td>
<td class="col61">0</td>
<td class="col62">0</td>
<td class="col63">0</td>
<td class="col64">0</td>
<td class="col65">0</td>
<td class="col66">0</td>
<td class="col67"> </td>
<td class="col68">0</td>
<td class="col69">0</td>
<td class="col70">0</td>
<td class="col71">19-08-29</td>
<td class="col72"></td>
<td class="col73"></td>
<td class="col74">20190721</td>
<td class="col75"></td>
<td class="col76"></td>
<td class="col77">V-SKFIT201925C</td>
<td class="col78"></td>
<td class="col79">J19070586</td>
<td class="col80"></td>
<td class="col81"></td>
<td class="col82"></td>
<td class="col83">29318284</td>
<td class="col84"></td>
</tr>
<tr>
<td class="col0">165551</td>
<td class="col1">378344</td>
<td class="col2">2401280</td>
<td class="col3">0</td>
<td class="col4">6</td>
<td class="col5">1</td>
<td class="col6">1</td>
<td class="col7">0</td>
<td class="col8">0</td>
<td class="col9">0</td>
<td class="col10">0</td>
<td class="col11">A </td>
<td class="col12">006</td>
<td class="col13">016</td>
<td class="col14">018</td>
<td class="col15">P01</td>
<td class="col16"> </td>
<td class="col17">5160</td>
<td class="col18">0</td>
<td class="col19">103200</td>
<td class="col20">103200</td>
<td class="col21">232.4</td>
<td class="col22">0</td>
<td class="col23">69.144</td>
<td class="col24">2019-08-29 11:38:52</td>
<td class="col25">2019-08-29 14:51:24</td>
<td class="col26">2019-08-29 11:38:52</td>
<td class="col27"> </td>
<td class="col28">TI39405 </td>
<td class="col29">0</td>
<td class="col30">0</td>
<td class="col31">4</td>
<td class="col32">0</td>
<td class="col33">0</td>
<td class="col34">0</td>
<td class="col35"> </td>
<td class="col36"> </td>
<td class="col37"> </td>
<td class="col38">0</td>
<td class="col39"> </td>
<td class="col40">0</td>
<td class="col41">0</td>
<td class="col42">0</td>
<td class="col43">0</td>
<td class="col44">0</td>
<td class="col45">0</td>
<td class="col46">0</td>
<td class="col47">29318308</td>
<td class="col48">0000165551.19082911:38 </td>
<td class="col49">0</td>
<td class="col50">0</td>
<td class="col51">0</td>
<td class="col52">0</td>
<td class="col53">0</td>
<td class="col54">2019-08-29 11:38:52</td>
<td class="col55">2019-08-29 11:38:52</td>
<td class="col56"> </td>
<td class="col57">0</td>
<td class="col58">0</td>
<td class="col59">0</td>
<td class="col60">3</td>
<td class="col61">0</td>
<td class="col62">0</td>
<td class="col63">0</td>
<td class="col64">0</td>
<td class="col65">0</td>
<td class="col66">0</td>
<td class="col67"> </td>
<td class="col68">0</td>
<td class="col69">0</td>
<td class="col70">0</td>
<td class="col71">19-08-29</td>
<td class="col72"></td>
<td class="col73"></td>
<td class="col74">20190721</td>
<td class="col75"></td>
<td class="col76"></td>
<td class="col77">V-SKFIT201925C</td>
<td class="col78"></td>
<td class="col79">J19070585</td>
<td class="col80"></td>
<td class="col81"></td>
<td class="col82"></td>
<td class="col83">29318308</td>
<td class="col84"></td>
</tr>
</tbody>
</table>
</body>
</html>解决方案
Aleft join用于返回左表的所有行，即使与联接表不匹配。
在您的情况下，如果存在 mo 匹配，那么该列statistics.tbl_g08t1.carrno将更新为，但我在子句中看到您希望在该列为（或 0）时更新该列。
此外，如果该列是因为与返回的任何比较并且是 never ，这将永远不会成功。
那么 的意义何在？
使其成为并在子句中使用正确的语法：null61_LoadUnit.carrnonullwherenullin (null, 0)nullnullnulltrueleft joinINNER JOINWHERE
UPDATE `61_LoadUnit`
INNER JOIN `statistics`.`tbl_g08t1`
ON `61_LoadUnit`.`StUnit` = `statistics`.`tbl_g08t1`.`ecarrno`
SET `61_LoadUnit`.`carrno` = `statistics`.`tbl_g08t1`.`carrno`
WHERE `61_LoadUnit`.`carrno` IS NULL OR `61_LoadUnit`.`carrno` = 0
或者
WHERE COALESCE(`61_LoadUnit`.`carrno`, 0) = 0
推荐阅读
- css - CSS - 动态改变字体大小以适应容器的高度
- compilation - 在 Gatling 中运行录制的模拟时，出现错误
- android - ionic 4 菜单不适用于 android 移动应用程序，但适用于桌面
- flowtype - 流动。如何根据函数参数指定联合类型
- java - CloseableHttpAsyncClient 不发出http请求
- angular - 如何以角度制作更新表格？
- c++ - 如何获得两个 std::map 的公共键？
- python - AES 在 Cryptojs 中加密，在 Pycrypto 中解密
- java - Spring 4.2 中的 JSON 响应压缩
- python - png 图像移动得太快了，我该如何放慢速度？（Python）