SQL Update实现使用一个表的数据更新另一张表

时间:2021-10-11 21:08:50
  • 表结构
  • 功能
  • SQL Serevr
  • Access

表结构

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAgkAAACjCAIAAACPGBoOAAAPNElEQVR4nO2d7bLcNg5E9ej7aHkz7Y8pO4o+KFBsggR4Tm25xpTcQOs2BjNJar3tAAAA/2Ub3QAAAEwHuwEAAM6wGwAA4Ay7AQAAzrAbAADgDLsBAADO9N0N28bugUUh/BAadgNAFwg/hEYf3+2Z653y6gADeQ1/eSIA5mHk9wZmAxLzFO/jOSMA09IlmvYvDcwGJOM1/OwGCMGw7w3sBsgN3xsgNOJoFv556+kDFLsBkmEMP/++AULQMZ3ld3/GAxJTCD/fGyAEvaJ5/JRkPATIAeGHBHT8d9HH3+7PI8F4QCYIP+Sgy79v2C8jcZqH4238kyXIQVX4iT1MTsd/pnSKPsMAi0D4IQHkFQAAzgh2w9N/sQfgTHuYCT8ERZ9tgcS2/ZMRfMWix3gQ/mQk9qXPtkAi7+Me3UIXEvtqDzPh/4GvWLAbXMFXLNgNQvAVC3aDK/iKBbtBCL5iwW5wBV+xYDcIwVcs2A2u4CsW7AYh+IoFu8EVfMWC3SAEX7FgN7ji7Ov3HymXT1SFhFKvap6+2sNM+H8QfotU7vCzGx7x93WtOPl4GNXcfLWHmfD/IPwqtbjhZzc8wnio1OKOB+EfWJHwV3Wiz7ZAgvHoVpHxqOqkPcyE/wfhV6nFDf/su2E7cHvetbRQ6qd2enGyYDmRNKNVO3Y41ld7mAn/3xJCKcIfMfwBdsPp19sX/Upr1U6dv/rq4bTTsF0d+ftqDzPh7yFO+MsvJJ3osy2QGDceXZFXOQoyHvJmVtsNXSH8VWopwx97NziU1qoVwmR8oepErpZyPAi/UI3wP72QdKLPtkDCfTx+r7vW/Uf9k/troRyjo6/jb4V+5eNx6vmfcb7aw0z4j6VVUoQ/Yvhn3w0DwVcs8u2GgeArFuwGV/AVC3aDEHzFYt7dADAD7WEm/BAUfbYFEiNm0oFt27b//ed/ozvSkNjXIkUdSBySrL70mgKJNcZjdDsyEvtapKgDiUOS1ZdeUyCxwHiM7kVJYl+LFHUgcUiy+tJrCiSyj8foRsQk9rVIUQcShySrL72mQCL1eIzuQk9iX4sUdSBxSLL60msKJPKOx+gWuoCv6EUdwFcs2A2u4CsW4XbDzD+ImXtrAV8VmgIJHnco8OVZ9HjD9oZdtjfDG+gEvio0BRI8boPUdfg/nKiaEUpdO7z+9vUeVTNyzcaiT1d/58dfa5Vvb7CcGCH8Fqnc4Wc3PKLydf08+O1ERT9f+59hqLpHRZTdcHwOltvs4hPuBsJfvkcFu8EVxsOu0/JaxWy74faduvBAat/Zbz+Qvp4YIfx2nazhj7QbOq3cQrlOgsZh2P7QqQ2hTtbxaCn6dGm78Fm89n3WDuG362QNf5jdcJstn4pyNT46RRmPlqKWfl6/GZQvRdkNhD9i+MPshr3bR4lCuU5SjEeU8Wgp+tpPyw3Xrx2WEzuE366TNfxhdkPc7w1XHcYjyng0Fr2+LZb5oFz1wg7ht+tkDX+Y3bC3fQ76Vk6lU/hMd72tcKLqRyj1wcX8vlRFO129DdLrSaFWbW9VOoQ/lq9/NQUSI2bSAXzFYsLdUL7h2yUfhjfQCXxVaAokeNyhwNfwop0+PAqZubcW8FWhKZAAmIP2MBN+CIo+2wKJvKs4618fmNXXIkUdSBySrL70mgKJNcZjdDsyEvtapKgDiUOS1ZdeUyCxwHiM7kVJYl+LFHUgcUiy+tJrCiSyj8foRsQk9rVIUQcShySrL72mQCL1eIzuQk9iX4sUdSBxSLL60msKJPKOx+gWuoCv6EUdwFcs2A2u4CsW4XbDzD+ImXtrAV8VmgKJ/o+703/A+1rUuaIP+PIserxhe8Moex0Hy0kVhCQW6+4GtyqdKt4O/4cTVTNCqW8uukR5vt3wdPV3fvy1XdlyYmd4SAh/bTN6TYEEu8Gsc307sJ+oGOtr7/YtMMpuOD4Hy21G5Tl3A+E/3R8o/OyG7hUZj6f7tcy2G27frAsPpOrNnd3Q3o9KJ2v4I+2GTiu3ULGTYNVHjH5tCHVOmgWn2h6e+nGj6u377/mJb+LXTfN6Yofw23Wyhj/SbvAsJy9UDkqOj07fXquIshvs91jEq17YIfx2nazhZze8V5RLJR6PV6faHp5qudGyG1puCLQbCH/htQp2g185YaGrTtbxsDjV9vBUy42qD/7bGx+UJ98NhL/8WsXqu+E6P70rqnSu83+1YzlR9SOUujVV61TVjFawvWjXq13zQ/gtUrnDH2Y3+IOvWEy4G8o3fLvkw/AGOoGvCk2BBI87FPgaXrTTh0chM/fWAr4qNAUSAHPQHmbCD0HRZ1sgkXcVZ/3rA7P6WqSoA4lDktWXXlMgscZ4jG5HRmJfixR1IHFIsvrSawokFhiP0b0oSexrkaIOJA5JVl96TYFE9vEY3YiYxL4WKepA4pBk9aXXFEikHo/RXehJ7GuRog4kDklWX3pNgUTe8RjdQhfwFb2oA/iKBbvBFXzFItxumPkHMXNvLeCrQlMgweMOBb48ix5v2N6wy/ZmeAOdwFeFpkCCx22Qug7/hxNVM0Kpby66RHm+3fB09Xd+/LVWuTfDQ0L4a5vRawok2A1mnevbgf1ExVhf+5+BkfTw1I8bH3bD8TlYbhsC4bfrZA0/u+ERxsOuc9Usu4gyHi1Fb59J4YE8PcMhEH67TtbwR9oNnb5mFsp1Eqz6iNGvDaHO09tc9PFoKfp0abvwQXy/i4flxAjht+tkDX+Y3XD7xH0qytX46BRlPFqKWvp5ffd/vVT1wg7ht+tkDX+83eCGsOJJivGIMh4tRV/7abnh6YFX6X8r3ShF+KOEP95uCPe9oTy0mcbD7iLKeDQWvb4tlvmgPPn3BsL/dL+WpXfD/me0fGrt0hhd5//6dmA5UfUjlPrgYn5fqqL9ru5342A5MUL4LVK5wx9pNziDr1hMuBvKN3y75MPwBjqBrwpNgQSPOxT4Gl6004dHITP31gK+KjQFEgBz0B5mwg9B0WdbIJF3FWf96wOz+lqkqAOJQ5LVl15TILHGeIxuR0ZiX4sUdSBxSLL60msKJBYYj9G9KEnsa5GiDiQOSVZfek2BRPbxGN2ImMS+FinqQOKQZPWl1xRIpB6P0V3oSexrkaIOJA5JVl96TYFE3vEY3UIX8BW9qAP4igW7wRV8xSLcbpj5BzFzby3gq0JTIOH7/2PhVo4YxWLO3XC8YXvDLnsUrD0xKtf+kRDgq0JTIOH7uCPuhtvh/3CiakYo9c1FlyjPtxuerv7Oj7/WKhdus5x8kP0M4Q8afnZD90KnrHw+UTHW197ty1+U3XB8DpbbahuYajcQ/tP9gcLPbuheiPF4ul/LbLvh9j268EC+vaezGz73o9LJGv5Iu+H2C1rXcp0Eq4Ilt9xvPK6/jT4eLUWfLm0XPojvd+NgOTFC+O06WcMfZjfcPnGfinI140eMv4MRazxqX6uIshvs91jEq17YIfx2nazhZze8V5RLGcejRydCtaeRfr1H2MNTLTdadkPLDYF2A+EvvFbBbvArJyxUjrvla7WkjUI/7TpPH/Gij0dj0eubRZkPypPvBsJffq1i6d2w/3e0fMqpdK6dX408nUh6OPUjlLo1VetU1YxWsL1ov6v73ThYTowQfotU7vBH2g3O4CsWE+6G8g3fLvkwvIFO4KtCUyDB4w4FvoYX7fThUcjMvbWArwpNgQTAHLSHmfBDUPTZFkjkXcVZ//rArL4WKepA4pBk9aXXFEisMR6j25GR2NciRR1IHJKsvvSaAokFxmN0L0oS+1qkqAOJQ5LVl15TIJF9PEY3Iiaxr0WKOpA4JFl96TUFEqnHY3QXehL7WqSoA4lDktWXXlMgkXc8RrfQBXxFL+oAvmLBbnAFX7EItxtm/kHM3FsL+KrQFEjwuEOBL8+ixxu2N+yyvRneQCfwVaEpkOBxG6Suw//hRNWMUOqbiy5Rnm83PF39nR9/rVXuzfCQEP7aZvSaAgl2g1nn+nZgP1Ex1tf+Z2AkPTz148aH3XB8DpbbhkD47TpZw89ueITxsOtcNcsuooxHS9HbZ1J4IE/PcAiE366TNfyRdkOnlVso10mwajzkrnuPx7Hh6OPRUvTp0nbhg/h+FwzLiRHCb9fJGv4wu+H2iftUlKsZPyj9zVms8TieRB+PlqKWfl7f/V8vVb2wQ/jtOlnDH2k3vH7OklfsJGUcjx6dCNVWGI+Woq/9tNxwHQfLiR3Cb9fJGv5Iu8GhSo+K5bg/xajlQ19tP+06ry4Kr1XMthuuV7c3PisbT+wQfrtO1vCH2Q37YbTcyql0Cp/prredTiQ9nPoRShldlE9UzWgF24v2u7p3ftSE3yKVO/yRdoMz+IrFhLuhfMO3Sz4Mb6AT+KrQFEjwuEOBr+FFO314FDJzby3gq0JTIAEwB+1hJvwQFH22BRJ5V3HWvz4wq69FijqQOCRZfek1BRJrjMfodmQk9rVIUQcShySrL72mQGKB8Rjdi5LEvhYp6kDikGT1pdcUSGQfj9GNiEnsa5GiDiQOSVZfek2BROrxGN2FnsS+FinqQOKQZPWl1xRI5B2P0S10AV/RizqAr1iwG1zBVyzC7YaZfxAz99YCvio0BRKOj7vTf8n7VMunkDP48ix6vGF7wy57FKw9MSrX/pEQ4KtCUyDh/mbtU1FY5Xb4b8e4/KdUzQilXn1ZnKqakWs2Fn26+js//lqrvN+Ng+WFEcJvkcod/pC7IVa5U1ZuT/bL57un19p+VDpPvixOVUTZDcY3bvtuqDoxQvjtOlnDz27oXs4YGuNrbT8qnad3JR9H/TRbit4+k8IDqX1nz7QbjK+1/ah0soY/zG7YDjiU2zv4uv2cmG88jj+j6OPRUvTp0nbhm/jpj1tO7BB+u07W8IfZDZ6FepRrGYlY43E8iT4eLUUt/ZTvqb1qOTFC+O06WcPPbvAod5JiPKKMR0vR134ab4iyGwj/6/3afmSaAgl2Q6VO1vGwnGh7eOrHjaq37+0Nu/LtDRPuBsL/dL+WpXfD0wh1rajSuTZ/9WI5UfUjlPrgYn5fqqJdrz4lqnBih/BbpHKHP8xu8AdfsZhwN5Rv+HbJh+ENdAJfFZoCCR53KPA1vGinD49CZu6tBXxVaAokAOagPcyEH4Kiz7ZcEQAAosNuAACAM+wGAAA4w24AAIAz7AYAADjDbgAAgDPsBgAAOMNuAACAM+wGAAA4838djdAZpneHVgAAAABJRU5ErkJggg==" alt="" />


功能

用表B的数据(mc列)更新表A的mc列


SQL Server语句

update A SET A.mc = b.mc FROM A ,B WHERE  A.bmbh = B.bmbh and A.xmbh = B.xmbh;

Access语句

update A, B  set A.mc = B.mc where A.bmbh = B.bmbh and A.xmbh = B.xmbh;

或者

update A INNER JOIN B ON A.bmbh = B.bmbh AND A.xmbh = B.xmbh SET A.mc = B.mc;