title: PySpark: writing in 'append' mode and overwrite if certain criteria match
body: <p>I am append the following Spark dataframe to an existing Redshift database. And I want to use 'month' and 'state' as criterias to check, and replace data in the Redshift table if <code>month = '2021-12' and state = 'ga'</code></p>
  <p>dataframe we want to append: <code>df</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">month</th>
  <th style="text-align: center;">state</th>
  <th style="text-align: left;">product</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ca</td>
  <td style="text-align: left;">ho0</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ca</td>
  <td style="text-align: left;">ho1</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ca</td>
  <td style="text-align: left;">ho2</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ca</td>
  <td style="text-align: left;">ho3</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ca</td>
  <td style="text-align: left;">ho4</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ga</td>
  <td style="text-align: left;">ho5</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ga</td>
  <td style="text-align: left;">ho6</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ga</td>
  <td style="text-align: left;">ho7</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ga</td>
  <td style="text-align: left;">ho8</td>
  </tr>
  <tr>
  <td style="text-align: left;">2021-12</td>
  <td style="text-align: center;">ga</td>
  <td style="text-align: left;">ho9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried the following script to append it, looks like it only append <code>df</code> dataframe, without replacing (overwriting) existing records for month '2021-12' and state 'ga'.</p>
  <pre><code>df.write \
    .format(&quot;xxx&quot;) \
    .option(&quot;url&quot;, &quot;xxx&quot;) \
    .option(&quot;dbtable&quot;, &quot;table1&quot;) \
    .option(&quot;tempdir&quot;, &quot;xxxx&quot;) \
    .option(&quot;aws_iam_role&quot;, &quot;xxxx&quot;) \
    .mode(&quot;append&quot;) \
    .option(&quot;replaceWhere&quot;, &quot;month == '2021-12' AND state == 'ga'&quot;) \
    .save()
  </code></pre>
  <p>I guess <code>.option(&quot;replaceWhere&quot;, &quot;month == '2021-12' AND state == 'ga'&quot;) </code> is not working. How can I make the change? thanks!
  (I also tried the following part, looks like existing records are gone, and replaced by <code>df</code>)</p>
  <pre><code>  .mode(&quot;overwrite&quot;) \
    .option(&quot;replaceWhere&quot;, &quot;month == '2021-12' AND state == 'ga'&quot;) \
  </code></pre>