Hello all,

Issue is:
its not inserting the information into the table, I am using this in
another part of my code and it works great but in this part I need to
use the ON DUPLICATE KEY UPDATE as there may already be the record in
the table, once the record is move I have it delete base on the
affected row 1 or 0.

the code I have is:

$query_is = "INSERT INTO irs_query SELECT * ON DUPLICATE KEY UPDATE
FROM irs_rawdata WHERE EIN = '$fin'";
$result_is = mysql_query($query_is);
$count = mysql_affected_rows();

Any help would be grrateful

Sincerely,
Christopher

    Should it not be:

    $query_is = "INSERT INTO irs_query SELECT * FROM irs_rawdata WHERE EIN = '$fin' ON DUPLICATE KEY UPDATE";

      Thanks for the respone!

      But it did not work.

      I am doing this:

      $query_is = "INSERT INTO irs_query SELECT * FROM irs_rawdata WHERE EIN = '$fin' ON DUPLICATE KEY UPDATE";
$result_is = mysql_query($query_is);
$count = mysql_affected_rows();

      Sincerely,
      Christopher

        Sorry, should have spotted this earlier, you need to specify which fields to update in the "ON DUPLICATE KEY" clause.

        Does this work?

        $query_is = "INSERT INTO irs_query SELECT * FROM irs_rawdata WHERE EIN = '$fin' ON DUPLICATE KEY UPDATE EIN = '$fin'";

          I want to update all the fields so do I put a * because I want to update all of them?

            No, I think in that case you would have to list all the columns you wanted to update (not a bad idea anyway as in the future you may add columns you don't want to update).
            If you have a recent version of MySQL, you might want to investigate the REPLACE command, although it's not an SQL standard.

              Ok the thing I am doing is:

              get the record in the table (irs_rawdata) checks the WHERE for $fin AND ... then it either needs to insert it or replace it if its in the table (irs_query) already.

              does that make sence?

              Sincerely,
              Christopher

                You can do that all in one query with ON DUPLICATE KEY (as you've been trying) or REPLACE. Try reading the MySQL manual.

                  Write a Reply...