Section 09 Modifying Data in MySQL/38 MySQL UPDATE JOIN.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "11d7cf7f-8824-467c-8305-c2e70694714a",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL UPDATE JOIN](https://www.mysqltutorial.org/mysql-update-join/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0a6c189-ce77-45d3-9dbc-0589d30cf9c9",
   "metadata": {},
   "source": [
    "Use the MySQL UPDATE JOIN statement to perform the cross-table update."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8b9ae0ff-3d4a-417b-8397-12811a717d23",
   "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": "markdown",
   "id": "94195e65-db55-40b4-ad3f-0ace1b6a8bf9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UPDATE JOIN syntax"
   ]
  },
  {
   "cell_type": "raw",
   "id": "094cae14-2ed3-4430-948d-02cfe618f6ef",
   "metadata": {},
   "source": [
    "UPDATE T1, T2,\n",
    "[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1\n",
    "SET T1.C2 = T2.C2, \n",
    "    T2.C3 = expr\n",
    "WHERE condition"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "511bb9d5-276a-4d96-b2ef-d6c5ce0507a3",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UPDATE JOIN examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6e6a5b00-cfc6-461c-b1fa-64c35363c539",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "# create mydb database\n",
    "# https://stackoverflow.com/a/30971098/2757266\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine_new = create_engine(\n",
    "    'mysql+pymysql://namlq:abc123@localhost/mydb',\n",
    "    connect_args=connect_args)    \n",
    "    \n",
    "if not database_exists(engine_new.url):\n",
    "    create_database(engine_new.url)\n",
    "\n",
    "print(database_exists(engine_new.url))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fedb3644-0397-4084-b596-425cb9687f2f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_7427/1097436567.py:1: 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_new.execute('DROP TABLE IF EXISTS employees, merits')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f08250216d0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine_new.execute('DROP TABLE IF EXISTS employees, merits')\n",
    "\n",
    "# https://stackoverflow.com/a/23293136/2757266\n",
    "# engine_new.execute('SET FOREIGN_KEY_CHECKS=0')\n",
    "# engine_new.execute('DROP TABLE IF EXISTS merits')\n",
    "# engine_new.execute('SET FOREIGN_KEY_CHECKS=1;')\n",
    "\n",
    "# create merits table\n",
    "string = '''\n",
    "CREATE TABLE merits (\n",
    "    performance INT(11) NOT NULL,\n",
    "    percentage FLOAT NOT NULL,\n",
    "    PRIMARY KEY (performance)\n",
    "); '''\n",
    "\n",
    "engine_new.execute(string)\n",
    "\n",
    "# create employees table\n",
    "string = '''\n",
    "CREATE TABLE employees (\n",
    "    emp_id INT(11) NOT NULL AUTO_INCREMENT,\n",
    "    emp_name VARCHAR(255) NOT NULL,\n",
    "    performance INT(11) DEFAULT NULL,\n",
    "    salary FLOAT DEFAULT NULL,\n",
    "    PRIMARY KEY (emp_id),\n",
    "    CONSTRAINT fk_performance \n",
    "    FOREIGN KEY (performance) REFERENCES merits (performance)\n",
    ");'''\n",
    "\n",
    "engine_new.execute(string)\n",
    "\n",
    "# insert data for merits table\n",
    "string = '''\n",
    "INSERT INTO merits(performance, percentage)\n",
    "VALUES\n",
    "    (1, 0), (2, 0.01), (3, 0.03), (4, 0.05), (5, 0.08)\n",
    ";'''\n",
    "\n",
    "engine_new.execute(string)\n",
    "\n",
    "## insert data for employees table\n",
    "string = '''\n",
    "INSERT INTO employees(emp_name, performance, salary)\n",
    "VALUES\n",
    "    ('Mary Doe', 1, 50000),\n",
    "    ('Cindy Smith', 3, 65000),\n",
    "    ('Sue Greenspan', 4, 75000),\n",
    "    ('Grace Dell', 5, 125000),\n",
    "    ('Nancy Johnson', 3, 85000),\n",
    "    ('John Doe', 2, 45000),\n",
    "    ('Lily Bush', 3, 55000)\n",
    ";'''\n",
    "\n",
    "# https://stackoverflow.com/a/33238549/2757266\n",
    "# engine_new.execute('SET FOREIGN_KEY_CHECKS=0')\n",
    "engine_new.execute(string)\n",
    "# engine_new.execute('SET FOREIGN_KEY_CHECKS=1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9032197f-1448-4a10-a659-ed500353faef",
   "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>performance</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   performance  percentage\n",
       "0            1        0.00\n",
       "1            2        0.01\n",
       "2            3        0.03\n",
       "3            4        0.05\n",
       "4            5        0.08"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM merits;', engine_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "85711a4c-9d5c-4162-8c75-b2a7dbf945d4",
   "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>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>performance</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Mary Doe</td>\n",
       "      <td>1</td>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Cindy Smith</td>\n",
       "      <td>3</td>\n",
       "      <td>65000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sue Greenspan</td>\n",
       "      <td>4</td>\n",
       "      <td>75000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Grace Dell</td>\n",
       "      <td>5</td>\n",
       "      <td>125000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Nancy Johnson</td>\n",
       "      <td>3</td>\n",
       "      <td>85000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>2</td>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Lily Bush</td>\n",
       "      <td>3</td>\n",
       "      <td>55000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_id       emp_name  performance    salary\n",
       "0       1       Mary Doe            1   50000.0\n",
       "1       2    Cindy Smith            3   65000.0\n",
       "2       3  Sue Greenspan            4   75000.0\n",
       "3       4     Grace Dell            5  125000.0\n",
       "4       5  Nancy Johnson            3   85000.0\n",
       "5       6       John Doe            2   45000.0\n",
       "6       7      Lily Bush            3   55000.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM employees', engine_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "13b7bed4-b32a-4498-86dd-931597ab6c22",
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_type(df):\n",
    "    return df.assign(\n",
    "        emp_id = range(1, len(df)+1),\n",
    "        emp_name = df.emp_name.str[:255],\n",
    "        performance = df.performance.astype('Int64'),\n",
    "        salary = df.salary.astype(float))            \n",
    "\n",
    "merits = pd.DataFrame(columns=['performance', 'percentage'])\n",
    "\n",
    "employees = pd.DataFrame(\n",
    "    columns=['emp_id', 'emp_name', 'performance', 'salary'])\n",
    "\n",
    "row = {'performance': [1, 2, 3, 4, 5],\n",
    "       'percentage': [0, 0.01, 0.03, 0.05, 0.08]}\n",
    "\n",
    "merits = pd.concat([merits, pd.DataFrame.from_dict(row)])\n",
    "\n",
    "row = {'emp_name': ['Mary Doe', 'Cindy Smith', 'Sue Greenspan',\n",
    "                    'Grace Dell', 'Nancy Johnson', 'John Doe', 'Lily Bush'],\n",
    "       'performance': [1, 3, 4, 5, 3, 2, 3],\n",
    "       'salary': [50000, 65000, 75000, 125000, 85000, 45000, 55000]}\n",
    "\n",
    "employees = (pd.concat([employees, pd.DataFrame.from_dict(row)])\n",
    "             .pipe(change_type))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29371156-e846-4ab1-9c75-150606c27b59",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UPDATE JOIN example with INNER JOIN clause"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0893a920-3237-4c6d-9935-b7a88afb695a",
   "metadata": {},
   "source": [
    "string = '''\n",
    "SELECT *\n",
    "FROM employees\n",
    "INNER JOIN merits USING(performance)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5767b6f3-00f6-4d26-a94a-7d2265c325bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "UPDATE employees\n",
    "    INNER JOIN merits USING(performance)\n",
    "SET \n",
    "    salary = salary + salary * percentage\n",
    ";'''\n",
    "\n",
    "engine_new.execute(string)\n",
    "\n",
    "df1 = (pd.read_sql('SELECT * FROM employees', engine_new)\n",
    "       .pipe(change_type))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "851c08e9-3095-45b8-b6f9-39a0dc0cdab5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = (\n",
    "    employees\n",
    "    .merge(merits, on='performance', how='inner')\n",
    "    .assign(salary = lambda df: df.salary * (1 + df.percentage))\n",
    "    .drop(columns='percentage')\n",
    "    .sort_values(by='emp_id', ignore_index=True)\n",
    "    .pipe(change_type)\n",
    ")\n",
    "\n",
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "888aa181-d516-4f7d-ab1e-83caaaf61121",
   "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>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>performance</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Mary Doe</td>\n",
       "      <td>1</td>\n",
       "      <td>50000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Cindy Smith</td>\n",
       "      <td>3</td>\n",
       "      <td>66950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sue Greenspan</td>\n",
       "      <td>4</td>\n",
       "      <td>78750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Grace Dell</td>\n",
       "      <td>5</td>\n",
       "      <td>135000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Nancy Johnson</td>\n",
       "      <td>3</td>\n",
       "      <td>87550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>John Doe</td>\n",
       "      <td>2</td>\n",
       "      <td>45450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Lily Bush</td>\n",
       "      <td>3</td>\n",
       "      <td>56650.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_id       emp_name  performance    salary\n",
       "0       1       Mary Doe            1   50000.0\n",
       "1       2    Cindy Smith            3   66950.0\n",
       "2       3  Sue Greenspan            4   78750.0\n",
       "3       4     Grace Dell            5  135000.0\n",
       "4       5  Nancy Johnson            3   87550.0\n",
       "5       6       John Doe            2   45450.0\n",
       "6       7      Lily Bush            3   56650.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17acec2b-79d3-40f7-a345-edbabca1d08d",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL UPDATE JOIN example with LEFT JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fbdb0be2-ff3c-4876-85ae-13d9b91f460d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0824f46370>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "INSERT INTO employees(emp_name, performance, salary)\n",
    "VALUES\n",
    "    ('Jack William', NULL, 43000),\n",
    "    ('Ricky Bond', NULL, 52000)\n",
    ";'''\n",
    "\n",
    "engine_new.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8bd8943d-11d4-4607-b164-26463864bb73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0824f46100>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "UPDATE employees\n",
    "LEFT JOIN merits USING (performance)\n",
    "SET salary = salary + salary * 0.015\n",
    "WHERE percentage IS NULL\n",
    ";'''\n",
    "\n",
    "engine_new.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b475e95a-c7d9-4d01-bd07-2899c2aa9d29",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = (pd.read_sql('SELECT * FROM employees', engine_new)\n",
    "       .pipe(change_type))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d59624cd-49e7-40e8-a92f-a079bcbce9e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "row = {'emp_name': ['Jack William', 'Ricky Bond'],\n",
    "       'salary': [43000, 52000]}\n",
    "\n",
    "df2 = (pd.concat([df2, pd.DataFrame.from_dict(row)],\n",
    "                 ignore_index=True)\n",
    "       .pipe(change_type)\n",
    "      )\n",
    "\n",
    "mask = ~ df2.performance.isin(merits.performance)\n",
    "\n",
    "df2.loc[mask, 'salary'] = df2.loc[mask, 'salary'] * 1.015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "15037ea3-4eaa-4a0f-9771-8d127ad131f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9f9b9570-2210-4998-8bef-d28895138187",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_id         0\n",
       "emp_name       0\n",
       "performance    0\n",
       "salary         2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e352db7b-9439-4f1a-8297-15254a2dac22",
   "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>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>performance</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Jack William</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>43645.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Ricky Bond</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>52780.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_id      emp_name  performance   salary\n",
       "7       8  Jack William         <NA>  43645.0\n",
       "8       9    Ricky Bond         <NA>  52780.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[df1.salary != df2.salary, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "76a71445-982f-4f09-85ab-2152dd7d39fa",
   "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>emp_id</th>\n",
       "      <th>emp_name</th>\n",
       "      <th>performance</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Jack William</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>43645.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Ricky Bond</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>52780.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   emp_id      emp_name  performance   salary\n",
       "7       8  Jack William         <NA>  43645.0\n",
       "8       9    Ricky Bond         <NA>  52780.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc[df1.salary != df2.salary, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9d148224-3a93-4dc9-a7c5-afb8de72304e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f0824f43400>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine_new.execute('DROP TABLE IF EXISTS employees, merits')"
   ]
  }
 ],
 "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": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}