Section 09 Modifying Data in MySQL/40 MySQL DELETE JOIN.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c4ed99ba-348e-4c77-931c-e6318249ea86",
   "metadata": {
    "tags": []
   },
   "source": [
    "## **[MySQL DELETE JOIN](https://www.mysqltutorial.org/mysql-delete-join/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "885402e0-6982-4b00-88f3-4e5d17fd8fe7",
   "metadata": {},
   "source": [
    "Delete data from multiple tables by using **MySQL DELETE JOIN** statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "234970eb-0977-4ad9-9d96-4903b858ad2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5000abb5-bfb0-4916-9729-554cb56cb083",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71548cfc-d92f-4f06-9430-76df77c4671f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL DELETE JOIN with INNER JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e9d928-b4c4-45b2-93b8-277476c8420b",
   "metadata": {},
   "source": [
    "To delete matching rows from both T1 and T2 tables that meet a specified condition:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "c0bfcbee-6828-450c-b92e-3da6b751ebdf",
   "metadata": {},
   "source": [
    "DELETE T1, T2\n",
    "FROM T1\n",
    "INNER JOIN T2 ON T1.key = T2.key\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8ad5a2d-6924-4064-a502-dd008bdfe5b4",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### MySQL DELETE JOIN with INNER JOIN example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "284ec005-6596-4943-85ed-50062276a207",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_13501/3530764196.py:3: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS t1, t2;')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f0272ee50>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make tables:\n",
    "\n",
    "engine.execute('DROP TABLE IF EXISTS t1, t2;')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE t1 (\n",
    "    id INT PRIMARY KEY AUTO_INCREMENT\n",
    ");'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE t2 (\n",
    "    id VARCHAR(20) PRIMARY KEY,\n",
    "    ref INT NOT NULL\n",
    ");'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "engine.execute('INSERT INTO t1 VALUES (1),(2),(3);')\n",
    "\n",
    "string = '''\n",
    "INSERT INTO t2(id, ref)\n",
    "VALUES ('A', 1), ('B', 2), ('C', 3)\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7a39bcd5-a255-4f8e-bac4-44fa75778f96",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWkAAAFrCAYAAAATs3hFAAAZp0lEQVR42uydW28TZxCG81/4MfyRnlzoQe1NL3pQWwkUzoe/AOKWS8AgQEQKUriwCCnNAQIENYJcpJgEgq1kuiP8CfPJXq9Dk313v+eRRiGJQYtm5vF4du2dMAAAkGXCAABAFiQNACAMkgYAEAZJAwAIg6QBAIRB0gAAwiBpAABhkDQAgDBIGqCPubk5e/ToEVEgWq2WQcUl7QV///59okBMTU0ZlI/n4uLFi0TBgIpLOnu2lSikqgTkg6T1ApB0UgH5IGm9ACSdVEA+SFovAEknFZAPktYLQNJJBeSDpPUCkHRSAfkgab0AJJ1UQD5VkPT8/Lw56+vrEjU17rF3u11zZmdnqVkRpCW9trZmzvLyskQRI+n6M0LSLjGPob9fWVkxp91u71vdXL582WL8GFdXV/13hf6NZrNpffj/g5oVoQRJFy54L/RQMBISRdL1p4qSvnfvng3BB52x/o3Nzc0gbWpWBCQtFDCCCqw7Spb0bo/DX62Gx1OzYkivO5A09KMg6ViG/nZ+nz6dra0t30XLStpF7McYr0P8sRH+/6JmRShV0oG+ExdeRKG4vPiRNPShJ2mXXYTMusOPzWUbemsALmokLY6MpL0oBoCkwQJqks7kGCZo3+H6VOo7YBlJu5jDY3zgCVdt9PWbizxM3qw7RJGRdK9I/OWiF7sXPZM0RGhKOhOb1E66f2oOJwAHgKQrgpyks6/spGEImpLOJmkfLCQm6ah3fJ3h3/sxDrv+GUmLIyfp3psAmKRhAFqSzqQst5OOfubHN+zkYOgtJC2OjKSzomInDbmoSTrsdsOU6l99Ut3ndxzG++VBe+ggYj/GuLfClR/huKlZMWQkHb0t1Z/RvXB4xyH0I3+ddGoBNZc0QcGrgaSpWTWQtFBAPkhaLwBJJxWQD5LWC0DSSQXkg6T1ApB0UgH5IGm9ACSdVEA+SFovAEknFVA6fs2xRC1UJaDikm61WhKFVJWA8vFJGooPYXvB9va2QZ+kq5jEOoIcNCAPn9bf3devbfG33+zF1au2GzaXl23pp5+s/fCh7ezsGCBpGYbJYafbNfuEYt3udAyQ9H70d7fdtsVff7W3v/9uzw4dshfXrtk4vH782Ba/+866R47Y0qFDiBpJazFIDp1Xr2w+K/qXV67seir568cfrf3ggQGS3sv+7qyv2+LPP9ubP/+0d+fPW/fsWXvy1Vcu6sK1uvjtt7Z17Nj7v3/qlC19/bX9OzubvKiRtAixHDrtti31ppKnWbG+HHMq2egVfac3lbyamzNA0nvV32+eP7eHX35pnRMnbCsT9NszZwqLOgj63bFjH/7uuXP2+LPPbO3WLTMkjaQV6JdDN5ugl375xd788ceHqaTRcFEXFvTCN9/Yu95U0jl50qcSJmokvZf97XXn64qPZRtqt9ksLugg9+vXkxc0khaiXw5vV1dtvtGIppJY1PmCjqeSJ198YWs3bxog6b3s782Cog6PXRgm6GYTQSNpLWI5bD55EmRb+OVjLOi4QQBJ70N/B/nm1uFG9JiovhE0ktbD5TCWdIOoCz4W/l9JX7p0ySYmJqzRaJgSfkwHDx60QFn9veFDhkv4+PGP6vFRJuFnFy7Y/Pff2xaCro6ks0L34kpa6kEOBUXtBe3yRdAlSdpFGOpWgTt37vixyEg6iNrrsl/UW6dP2z/ZzzaOHv2oVpe9nllxaEr6wIEDXlhIOshhDFE/PXzYnmZTydIPPyDofZS012mQs9evT9Vl48czOTnpTxwyknY24rVdJul3587ZVlanXqudLHprkOQvtZOUdFZQXuQ+BSDpIIdioo6nEgS9j5J2GQYRhj+roCZpZ3Nlxbx230xOmsvZRe3h9bvcaPgJbQStKukeSDqSQ2FRx1PJ+zUIgt7bPITpWbJ2kXT9QNIiFN2Fbj57Zn8fPjyw4Bc//9xe3rhhsHd5CLUaS9sn6gIkJ2nWHUi6NiBpDTwPRU5yx+GiHk1akubEIZKuFaw7NBiVB6/TsOqITyR6HeeTjqS5BA9J1w5OHGrgeRhxbfSwPbXENdORpHkzSw1A0iJwCZ4GnoddCNB30kHgZTBsBcPbwmtACZLO3e/JvGy0GLU3szQavJmFt4WPgg9YqgESkzTwtnAVkDQfVaoGkhaBD1jSAEnzof9qIGkR+KhSDZA0t89SA0mLEH3ov08lfOi/xSBpJbgRbQaSTgdun6UBki6/v7e3tw2QtBzciFYDJE1/q4GkRRgmh51u95PObm93OgbFmZqa8lwQBeL27dsGFZd0q9VyUROjg4IXgUmaIUwNJmkRkIMG5IH+VgNJi4AcNCAP9LcaSFoE5KABeaC/1UDSIiAHDcgD/a0GkhYBOWhAHuhvNZC0CMhBA/JAf6uBpEVADhqQB/pbDSQtAnLQYBf3OJS4CW10XH4XGSRdE0qVdLgjSwi/60WqIGkNikja6zSuYf9aAuEY/Jjiu8R4/yHpGlCmpP32PuEx/lVmKhkAkk6EcSQd35xWid4xIekaILXuyBog2WkaSWswpqRdhHL35+y7ezmSrgFSks6KX+KOywNA0omwi520XM2GdYexk64FMpLOHiv5sjGApNOg4CQdr+1kXgFmfcSJw5ohI+msyGUKfRBIOg3GkXS88hCg/7wOkq4JEpL2wvdpJGWQtAZVlnS0ekHSNaFsSXtRSRT4CJB0Iuxy3TFSjnUTNJIO1FzSCBpJq1HFE4fhRGEU/uSBpGtAaZIOJzjiCJcOpQaS1oA8IGk1JHbSgBxUIA/0txpIWgTkoAF5oL/VQNIiIAcNyAP9rQaSFgE5aEAe6G81kLQIyEED8kB/q4GkRUAOGpAH+lsNJC0CctBgenra65YoEHfv3jWouKRnZmZcPkSBaDabBiPgyVIKhjCzykuaJCKHqkEe6G81kLQIyEED8kB/q4GkRUAOGpAH+lsNJC0CctCAPNDfaiBpEZCDBuSB/lYDSYuAHDQgD/S3GkhaBOSgAXmgv9VA0iIgBw1y8hDfjUXqM9CjD/7375F0TShT0vEH/yd9n0MkrYHnoUDNxhL0n5UlLX+CCLf0Ct+HJw4kXQPKlLRL2R8jda+4YSDpNBiRB5dhJWo0k7TfIBdJ1wCpdUe4yWeKIGkNPA859Syx2ii6+mDdUQ9kJO2PDc/+KYKkNcjJQ1gjSMop3klnIOmaILWTTlXQSFqHqko6Ok7WHTVCZpLu7aRZd0AurDt0VodIOiM1SVdlUkHS9WVIHip3chtJ1wckLQKS1sDzMKJG5S7B82Px6Os71h01oixJ++98Kokvb2LdAUPQyUNPglJvZomPxwXNicN6UJakw0uy/sJKVtBIWgfygKTVkFp3pAxy0IA80N9qIGkRkIMG5IH+VgNJi4AcNCAP9LcaSFoE5KABeaC/1UDSIiAHDcgD/a0GkhYBOWhAHuhvNZC0CMhBg1ar5bkgCsT09LRBxSW9sLDgoiYKxMzMjAEAxEwYAADIgqQBAIRB0gAAwiBpAABhkDQAgDBIGgBAGCQNACAMkgYAEAZJAwAIg6QBAIRB0gAAwiBpAABhkDQAgDBIGgBAGCQNACAMkgYAEAZJAwAI8x97d0PcNtaGYbgUFkMplMNCCAZTCINlUAZBEARLIASWQTnk87Pzva32jK2e1n+vouua0aR1FNuy49vKsa0j0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzR280j/+eef758+ffrP8vnz5/fD4fD+999/v5+Qdc4tOb+z68/466+/1s6/rlOd78yysr3j4jkR2ECkl8vLy4tIAzwy0mP83t7esic9hrGMMR5dGunxMq91ObW9YgxsO9Ll+fn53+89PT2JNEC3SP/zzz8VPpEG6BbpOBPpc0sie8sx6WtEenU8HUCkRRr4INpG2nAHwGMjnXd51PumRRqgW6Tzro5873A4iDRAl0i/vr5+P/2PP/7IuzxEGqDbJw4T6Ax5XOETh2vL733icD7SPnEIfKxI57SvX7++f/v2LauINICj4AFsj0gDNCbSAI2JNEBjIg3QmEgDNCbSAI2JNEBjIg3QmEgDNCbSAI2JNEBjIg3QmEjfQc2MniPk5Sh8M3KEwKybWWvq6HxbMbuttZ2Z9CGHra3D1+b/OQ9ApO8lka2JdGfiXNHKsrlIz25rQlzbOS45PbcF7J1IH3UK1/E42xWprJs90g8b6UQ42/fy8pJ/189+/+shtwXsnUjfPlarSwV4HCrI16PNRPrXtnVdol2Rh70T6ebh2mOkj3vQIg0ivdBoCGCrkZ7f1vltzvyXsHcifdQ5XHuLdO1FPz8/vwMi/a/O4dpTpGssOm/BA0T6u87h2kukj+sKNIj0wyRc9Wf8biI9ua0Js0CDSD9UXgSr9z8nYo+OdN7il/cj5/pkmOER21rvkzYGDSLdw5cvX1beljb1VrZrBbsup2L6iG2tIY5x2ewnLUGkNy57r3WcikdHOp6enuo8b7mtIg0iTfzm292Oi18D6Mqjc6eOe6gZkvCCHTQn0ju0GE5IqB1tDhoT6R2qOB/HhAUamhNpgMZEGqAxkQZoTKQBGhNpgMZEGqAxkQZoTKQBGrtHpOtwlOeWtQlN6yhtORjQ2Tnv8oGMw+GQQ2/Wz4zrj+edn3EcC6C9TUR65ahoOdpaIn52/dfX11MhzjGUx0NrijTQzv0iPQZ2fuqlhDgRrT3k8bzr9Ky3PExm7YXXXnPOs9bNMh6kPj8j0kArm4j08vSc1xjXOm2UENdec0W6/p94l+fn56yb74s00MqmI72M7gkZ6qipmWr9Go9eDoVkLDvnke870DzQyqPHpPO9tUiPxz7OuPK4p7z2gmJdxjLS9bMZ3sjPVrhFGminbaRPLInqyfNdUZcxrp+96Ax5ZC+7zlekgXbaDneMSwJafndPOl8T5pJ/L4Y9spcu0kArbSOdKA/v7BjHnrNORXZ1TDrr1fXIUmqi1MVlizTQSvtIR40b19vphgjn/GcutyK9etk17g2wTqTfS+31njq9PmWY7y/fJ11DIdkLL/n3TKRzGQDrRHocX86edJZEeOaFxqy7HK/OaSINbMpmIh05rfaaFxLi7DnXC4HZu846NTQi0sBm+XgdQGMiDdCYSAM0JtIAjYk0QGMiDdCYSAM0JtIAjYk0QGMiDdCYSAM0JtIAjYk0QGMifR85vGqOwDd7hL2aGDdH9DNbDOyYSN/D/GFQK8512NUsIg07JtJHnSJdk+EeI511s/ct0rBjIn37MK8uFeBxWCRfj0Qadk6km0V6QaQBkf5Xo+EOkQZEeiDSQFcifSTSQFcifSTSQFciHXeM9PPzs0gDIt3N29tbvf85wRVpQKS7+fLly8pb8FbftufTh7BTIn1H+YDK4XDI3rRIAyINsHUiDdCYSAM0JtIAjYk0QGMiDdCYSAM0JtIAjYk0QGMiDdCYSAM0JtIAjYk0QGMifXs/O6rdyeNHf/78+X2UWV1qdpfxvA+Hw8SxqOs8po+wd3KdnO/r6+vNb5tTvn37lm3N7fN9vaenpxyv+9z2n1pWb5ccrXAp69V2XyqXk+s+I7fx8vC2+ffLy8s7+yLSTSOdJQ/IyUhXXO4R6VpyXveMdLavDvN6aknULo10PeHdLNJ1nX4iM/is3u7sh0jf1yJAqw/ifM2e00yks17ilQf2dKRz2oQxTrUnW7PM3PM2qu3JnnOCXeFeXp9cv9+Z4aZul1ov5/PQSOcJuv5CqNu9wp3tZD9E+odWkc6DtKKyEumKR0JVkbplpMftuNdtlFithTJBq9vtkkjX7Z7/Xxbp+T362fOO/w/zeNjuiXt7oVOkI9HN/ycinYjV9z5kpBcRPTd+WxP9XhLpfD/Brye8dpE+Xi+R3hn39kK3SCccNdb8k0jXz2ZP65Ix6Z9FOtel9lrz9V63UV1m/fk/SlDrul4a6Sz1hPCw4Y5RXY+63dkPkf6hXaQTnuw5ZShjItJDXK4W6XGpcfBcv9vfRvNxu1akl094bSKd2zrXJ78PeaJkP0T6h3aRjgS69qZXIr0cs8xyveGO9T/NP+SedNTYdL4+OtLZtnpxONvPvoj0Dy0jnThXmCcivXzB8apj0uM7O/I17jwmnbHntTHprHeNSNcTXn7ukZFOlAV650T6h5aRjnrnxkSkKy7Z87zZW/By/nUeN1Af5hkjPLunfLVI1xPe169frx3p3I4zgc79LtA7J9I/tI10wlHhmoh0vl/rXzvSFa9Lo1XblSegBGhqTz3bU9+rcdl6IbPGyeNKka7XBHK514p0rmttw1qocxvXZQv0zol0008cnonMTKQrLvnepZ84PBennFbDD5dE+tR1qD3HmfXHn7n0E4d12nj6tSKd22tmnH/qLXvsg0hvJNKJx2SkKy43i/S4d3+B7DFmD3g8DsfaEEC9j7kuf9grvWqk6wnvmmGsbRZpRBpg60QaoDGRBmhMpAEaE2mAxkQaoDGRBmhMpAEaE2mAxkQaoDGRBpg/9spyWjuRBi5XxyDJ8UlyrJPx+CVMS5x/97bL+nXsHZGmn+yBLH7BE4uTv6xZp75fVibiveiQoVkmjHHLz20ubovbflORrvvpNw5elfut1Z60SNNWZvBemSvxXEzygLtVpGePJFcP+E0GbnHkwLqtN3ds6rqfZuK8fCKt35NORJqW6hczex8V3cUeSf3SjpHO10Tl4ZE+HA65vOUkA/m5Tc3afbwPWkbrSvdTPQnVZArZKWi5vSJNSwnZsNc5PriyBzRGejlX41ykLz+u9+x5J9QVj6tFaBwzzhNUnsgG43o1m/zaDOJZv2W0rng/1VyU4/be8jr96msA+bdI088Q4VFiVHP+jZMe1Nhfu0jnyeVKka4gZzk120zFtx74td7aunU73mbigPnzTzTvfT+1iPTK/STS9JKY1bDAynh1PaDHSNeDLfF50HDHegQyQe2FlpHN+eYBniW3WV3GeLm151xBWJ7+ISI9eT/NRPoR85TW5Y+zDOW+FGl6mRiDy/fORToBqj/n20R6+SLcFQzzM67fdvmLJMsJuT45nw8x3LH1SNcM78ffX2PS9HbhnnQk0LU3/ehIZ3vy4EsQ8wC8X4TmhwFE+vGRruvshUO2ofY6fzYmXYZIJ871i/3ISOdFvDHQu4604Q6R5oNI1M48YHPa+As9Rrr2phPI60Z6fsii3mkyBPr+ka4niRUfMtLHbd5cpOs6D7/HIk0/FbgEJnuji9PrfdI5fSXSFfPptzDNBm/mxb8ajqm3yf3qnneNN18j0vUgrxek2kRrxW3vp/ntnb+fLo90Al3Xuf4azH3m3R30tbLXlfCcWnftPC6PTcV3/e1ReXKYeSvY+jbXNl4e6TzYE5O16/LhIj1xP8Vt7qf534Mx/KfWybaINH3lF7P2MCpMtQc9Eek8UK4V6fHDBpdG+l570nW9E5Nc711EeuJ+iiveT5dHevyYer7m/0ciDfDRiDRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNfXrwHHe1Tr4Ox2qdOr+c9qtTv4/r5OhsE4cyPLV4jgM+eKRzYOwHRnqcPVmkgVY+3fhYsGP8RonoxZGu0yZUdE89UWTJMWXH9cUYOGnzkY7JSCfoj4j0eHrOT6SBNu4e6cz1lTHgMdJHIg1w50hnWpqEboxw5mtbzmE2P239/Jj0VHRrep6aLLXMjmEDbDrSCVniV/OJZb6v/D/jwMP37xnpcak5zkQaaOUekU7Qaq85k2hmyCNLfT/LvYc7aqmJImvPPgx3AG3cM9I1a26WerHwUZEe39mRcfJcH5EGWrl1fbLnXGPQFeYs+Xdi/ehI14uZdR4iDbRy8/okfLWnmqXk3xXADIM8MNJ5Aqlx6TyBiDTQxr0iPYY0wwzj6Xf9xOEg69UThk8cAm3cNdIJanl7e2sV6eMedPaka2hGpIEWVAagMZEGaEykARoTaYDGRBqgMZEGaEykARoTaYDGRBqgMZEGaEykd6wmXMhkDEBPIr1TOebJ5AwzJkOAM0T6A8lBmxK2yUOq1tyLmSihDk7Vak96S5HObff09GTKMzZpG4+yD2D2uNcV58RznKuxky1E+nib5Xqal5JN6/0o+0AmI13H2U6ks26iItK/J7dhhTm3q0izSb0fZRtXYV5bKsDjsEi+xpUjnfCvH3N7fbignkCytI90JnBInGsbZiOdY51n+2reSzhHpDfuFyM9ahHpmjT4zHLp3vjUbXOx+UjXbdR2mOl/7d1tTsJAFIbR9XYp7tq+hvunsZ2EzzvDOYmJpqhg5OlwW4DvI9J/+ow7nhrpcYyurlO9W00+Eut8Xav9rDKXjbSVNJ2I9E6kf87m48u8i/r+9zOTZkrz3MsmN2Okb9vzu0Ua/iPS6xDpecYd0IlIx/sinTMOpot0PdnlMLMVaRDpdSRsdfpagvGKSNdlt217RqQT52zPAbQcLKyzPSrQy447bjshr2tCC/PcyxaQ2A1Wi/n8ntXl8fsS1XtWrwn26DonXtkRtI/04LZm++UB09pBwRmRXkzCmbglcg9HerySfjTSdRpePZGlfnZuRy63bKR3ifN0jxZYk/9AuBgF5QWu4IRIwydkDl2PdhJrOCHS8EY1ajKPphWRhkOkE+ccPIQBkQb4diIN0JhIAzQm0gCNiTRAYyIN0JhIAzQm0gCNiTRAYyIN0JhIAzT2C7mcnpV2npfLAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/MySQL-DELETE-JOIN.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c4a410c3-e277-4ecb-baec-c25e172e9d68",
   "metadata": {},
   "outputs": [],
   "source": [
    "t1 = pd.read_sql_table('t1', engine)\n",
    "t2 = pd.read_sql_table('t2', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d2d39cf8-fc8e-41b4-bf38-d24f3bc11e97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "23a32fed-f30b-4185-a780-779e6f0c788d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id  ref\n",
       "0  A    1\n",
       "1  B    2\n",
       "2  C    3"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a0348a9b-abfe-4cb4-849c-d73279d3f757",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f026d3400>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "DELETE t1,t2\n",
    "FROM t1\n",
    "INNER JOIN t2 ON t2.ref = t1.id\n",
    "WHERE t1.id = 1\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4a8730ac-b791-4faf-b0b1-52b35ddb14d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   2\n",
       "1   3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM t1', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ffdae7f0-21e1-4fa8-ba0a-df6a7e05b9c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id  ref\n",
       "0  B    2\n",
       "1  C    3"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM t2', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f81e34fc-b55d-4140-b205-a8551173ddc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "inner = set(t1.id).intersection(set(t2.ref))\n",
    "\n",
    "t1 = (t1\n",
    " .query('id in @inner')\n",
    " .query('id != 1')\n",
    ")\n",
    "\n",
    "t2 = (t2\n",
    " .query('ref in @inner')\n",
    " .query('ref != 1')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "75b58058-a2f5-46a5-ad2b-a361a69371b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "345c97b2-ff54-4fe5-ac28-b3e6b223cf3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id  ref\n",
       "1  B    2\n",
       "2  C    3"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "60f0a865-8835-4b8b-a94d-7524f0f3bf6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f026222b0>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unused tables:\n",
    "engine.execute('DROP TABLE IF EXISTS t1,t2')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "147a6ad7-d632-4c0b-8715-b6fee0f56d5a",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL DELETE JOIN with LEFT JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cc8e769-8851-4c27-b8c1-914a3511bac8",
   "metadata": {},
   "source": [
    "Use DELETE statement with LEFT JOIN clause to delete rows from T1 table that does not have corresponding rows in the T2 table:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d7130700-0bd0-4cb6-ac59-45a9841e5c04",
   "metadata": {},
   "source": [
    "DELETE T1\n",
    "FROM T1\n",
    "LEFT JOIN T2 ON T1.key = T2.key\n",
    "WHERE T2.key IS NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353518f3-86b7-4992-b351-65a7dff95a59",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### MySQL DELETE JOIN with LEFT JOIN example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a46ebe8d-3b30-47d5-be78-cd3d35d2fed5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f0272ee20>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make a copy of customers tables\n",
    "engine.execute('DROP TABLE IF EXISTS custom')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE custom\n",
    "AS\n",
    "SELECT * FROM customers\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f78e887b-41b6-4a21-81a8-26ee13827087",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f0265e220>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Removes customers who have not placed any order:\n",
    "string = '''\n",
    "DELETE custom\n",
    "FROM custom\n",
    "LEFT JOIN orders ON custom.customerNumber = orders.customerNumber\n",
    "WHERE orderNumber IS NULL\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c1340d13-26c1-4eb3-83c9-28d210468532",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>orderNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [customerNumber, customerName, orderNumber]\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# verify the delete:\n",
    "string = '''\n",
    "SELECT c.customerNumber, c.customerName, orderNumber\n",
    "FROM custom c \n",
    "LEFT JOIN orders o ON c.customerNumber = o.customerNumber\n",
    "WHERE orderNumber IS NULL\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b4256649-62f2-4319-8297-fa13af2b1d03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0f0262b1c0>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read the table after deleting rows\n",
    "df1 = pd.read_sql('SELECT * FROM custom', engine)\n",
    "\n",
    "# delete unsused table\n",
    "engine.execute('DROP TABLE IF EXISTS custom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7a5c8a54-83f1-4f4f-b96b-0c850d7c03db",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the origin table\n",
    "df2 = pd.read_sql_table('customers', engine)\n",
    "\n",
    "# read customerNumber column of orders table\n",
    "orders_cus = pd.read_sql_table(\n",
    "    'orders', engine, columns=['customerNumber'])\n",
    "\n",
    "# difference of two sets\n",
    "left_cus = set(df2.customerNumber).difference(set(orders_cus.customerNumber))\n",
    "\n",
    "# remove customers that have no orders\n",
    "df2 = (df2\n",
    "       .query('customerNumber not in @left_cus')\n",
    "       .reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8f98a4f9-f5f4-4663-88f4-b822dd10eac7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aad292ac-b587-440d-814a-df3b0c7de6e3",
   "metadata": {},
   "source": [
    "Why the two tables are difference?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7d3f1f2e-6292-4833-aa4e-c13910e9092a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "addressLine2    78\n",
       "state           52\n",
       "postalCode       6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()[lambda ser: ser != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8a0d8985-da64-4a1e-ace9-2b0368d09a74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "addressLine2    78\n",
       "state           52\n",
       "postalCode       6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna().sum()[lambda ser: ser != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a40f50af-54a2-4008-9fa0-7cbf488e84b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "addressLine2    78\n",
       "state           52\n",
       "postalCode       6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.isna().sum()[lambda ser: ser != 0]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "toc-autonumbering": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}