0

I was trying to use the ON UPDATE DUPLICATE KEY clause for the first time, following this link

SQL - IF EXISTS UPDATE ELSE INSERT INTO

and I'm getting an error in my sql syntax:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AJAY KUMAR')' at line 2 The SQL being executed was: INSERT INTO fee_acc_balance (guardian_name, account_no, paid_amount, due, days_overdue, total_fees, updated_on) VALUES ('AJAY KUMAR', '10', 0, 12550, 0, 12550, '2017-02-10 21:28:05') ON DUPLICATE KEY UPDATE guardian_name = VALUES ('AJAY KUMAR') Error Info: Array ( [0] => 42000 [1] => 1064 [2] => You have an error..

The unique key in my case is account_no, and this is my sql :

INSERT INTO fee_acc_balance (guardian_name, account_no, paid_amount, due, days_overdue, total_fees, updated_on) 
VALUES ('$father_name', '$account->account_no', $payments, $sum, 0, $sum,'$now')
ON DUPLICATE KEY UPDATE guardian_name = VALUES ('$father_name')

Where does the error lie?

Community
  • 1
  • 1
Ramesh Pareek
  • 1,601
  • 3
  • 30
  • 55
  • Can you add the variable definitions to your question? Where does $father_name come from? – Nathan Feb 10 '17 at 16:17
  • this sounds as if you are asking if the apples are ripe or not, when I ask you the sum of two apples and three apples :) ! But if you must know before you answer, $father_name is coming from another database call. all above variables are being calculated with various database and function calls.. I'm sure they are unnecessary to be able to answer this question. Trust me the info is sufficient to answer. – Ramesh Pareek Feb 10 '17 at 17:53

1 Answers1

2

You cannot specify an absolute value in ON DUPLICATE KEY UPDATE:

ON DUPLICATE KEY UPDATE guardian_name = VALUES ('$father_name')

Try with

ON DUPLICATE KEY UPDATE guardian_name = VALUES(guardian_name)

Notice that the right part of the assignment is the new field coming in from VALUES, and the left side is the extant record; "UPDATE a = VALUES(a)" means "put the a from VALUES into the record", not "leave everything as it is".

Also, you may want to write variables in curly brackets:

...VALUES ('{$father_name}', '{$account->account_no}', {$payments}, {$sum}, 0, {$sum}, '{$now}')

or even better use PREPAREd statement with PDO:

$stmt->prepare("INSERT... VALUES(?, ?, ?, ?, 0, ?, ?)");
$stmt->execute([
    $father_name,
    $account->account_no,
    $payments,
    $sum,
    $sum,
    $now
]);

and, better still, bound parameters.

Otherwise, strange things might happen if the guardian name is Ajay Al'Kumar (note the quote mark) or a string value is passed instead of an integer one.

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • what I don't understand is where do I tell mysql that guardian_name is $father_name? Will mysql automatically pick values from my insert statement when I write `VALUES (guardian_name)` in ON DUPLICATE KEY UPDATE statement, instead of `VALUES($father_name)` ? – Ramesh Pareek Feb 10 '17 at 17:43
  • 1
    You don't need to. You are trying to insert $father_name into guardian_name, so, yes, MySQL already knows that VALUES(guardian_name) is 'AJAY KUMAR'. So you need only to specify that VALUES(guardian_name) must go into `guardian_name` (the field). – LSerni Feb 10 '17 at 17:49
  • can I also supply values for multiple columns at once ? e.g. `ON DUPLICATE KEY UPDATE (guardian_name, account_no, blah, blah-blah) VALUES (guardian_name, account_no, blah, blah-blah)`. I observed that this method does not work. How do I do it? – Ramesh Pareek Feb 10 '17 at 17:56