0

Hi guys i m trying to write a mysql statement with the IF row count function within the sprintf function but to no avail.

EDIT: This statement is basically for importing an excel spreadsheet, and then processing the data. It checks for duplicates based on the fields that aren't the primary key so the check is needed

Based on the method to insert on duplicate update for a non-PRIMARY key posted here - MySQL insert on duplicate update for non-PRIMARY key

    $sql = sprintf("UPDATE tbl_jobs SET a=% WHERE b=%s 
         IF ROW_COUNT()=0
    INSERT INTO tbl_jobs (...) VALUES (...) "),
 $varA, $varB");

i believe the problem happens when there is a function nested in the sprintf statement

how do i actually escape the function's brackets in this case? or is this statement wrong to begin with?

any help would be appreciated.

thank you!

user3448267
  • 191
  • 1
  • 1
  • 15

1 Answers1

0

Your syntax is not valid syntax.

Presumably you want insert . . . on duplicate key update:

INSERT INTO tbl_jobs (a, b, ...)
    VALUES (...) 
    ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b);

For this to work, you need unique indexes/primary keys set up correct (for identifying the duplicates).

You can define a unique key as:

create unique index unq_jobs_a_b on tbl_jobs(a, b);

Then the first statement will do the insert/update as you need.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • just updated the post to include this info,, this is because the statement is only considered a duplicate based on a match of 2 fields (non primary key) – user3448267 Jan 26 '18 at 02:08
  • @user3448267 They don't have to be the primary key, any unique index will work. – Barmar Jan 26 '18 at 02:09