Insert/Update Help

focus310

Hello:

I'm trying to write a query which will update values after they were inserted.

Once a set of rates is inputed for a new lender and program, the rates will be inserted into the table. Later, if there is a new set of rates for the program/lender which was just inserted, I want to update those rates.

When I run the query I get the following error message:
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 '(program_id = '1', program_code = '30YFA', lender_id = '1', lender_' at line 7

Thank you for the help.

<?php

//session_start();
include('header.html');

require_once ('mysql_connect.php');

$query2 = "INSERT INTO rates (program_id, program_code, lender_id, lender_code, rate, price, date, time)
           VALUES ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate1]','$_SESSION[price1]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate2]','$_SESSION[price2]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate3]','$_SESSION[price3]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate4]','$_SESSION[price4]', CURRENT_DATE, CURRENT_TIME)
           ON DUPLICATE KEY UPDATE 
           (program_id = $_SESSION[program_id], program_code = $_SESSION[program_code], lender_id = $_SESSION[lender_id], 
           lender_code = $_SESSION[lender_code], rate = $_SESSION[rate1], price=$_SESSION[price1], date = CURRENT_DATE, CURRENT_TIME),
           (program_id = $_SESSION[program_id], program_code = $_SESSION[program_code], lender_id = $_SESSION[lender_id], 
           lender_code = $_SESSION[lender_code], rate = $_SESSION[rate2], price=$_SESSION[price2], date = CURRENT_DATE, CURRENT_TIME),
           (program_id = $_SESSION[program_id], program_code = $_SESSION[program_code], lender_id = $_SESSION[lender_id], 
           lender_code = $_SESSION[lender_code], rate = $_SESSION[rate3], price=$_SESSION[price3], date = CURRENT_DATE, CURRENT_TIME),
           (program_id = $_SESSION[program_id], program_code = $_SESSION[program_code], lender_id = $_SESSION[lender_id], 
           lender_code = $_SESSION[lender_code], rate = $_SESSION[rate4], price=$_SESSION[price4], date = CURRENT_DATE, CURRENT_TIME)";




 $result2 = mysql_query ($query2) or die (mysql_error());


?>

bpat1434

It should be something like:

INSERT INTO table (column, ...) VALUES (value, ...)
ON DUPLICATE KEY UPDATE column=VALUES/b

X will be replaced with a letter (a-z) based upon the position in the VALUES() part of the insert clause. Basically, what this will do is on each insert update the information with that specific INSERTs values. A better example:

<?php

$query2 = "INSERT INTO rates (program_id, program_code, lender_id, lender_code, rate, price, date, time) 
  VALUES ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate1]','$_SESSION[price1]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate2]','$_SESSION[price2]', CURRENT_DATE, CURRENT_TIME),
                  ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate3]','$_SESSION[price3]', CURRENT_DATE, CURRENT_TIME),
            ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate4]','$_SESSION[price4]', CURRENT_DATE, CURRENT_TIME)
  ON DUPLICATE KEY UPDATE program_code=VALUES(a), lender_id=VALUES(b), lender_code=VALUES(c), rate=VALUES(d), price=VALUES(e), date=VALUES(f), time=VALUES(g)";

$result = mysql_query($query2) or die(mysql_error());
?>

That technique was lifted directly from the mySQL documentation 😉

focus310

Thank you for the help. I will give the technique a try. I read the documentation was confused with ON DUPLICATE KEY portion specifically the a-z letters.

bpat1434

Using their example it's pretty easy to see:

INSERT INTO table ('column', 'column2', 'colum3')
VALUES (1,2,3), (4,5,6), (7,8,9)
ON DUPLICATE KEY UPDATE 'column2' = VALUES/color + VALUES/color;

Using that example, what it's saying is that for each insert statement ( (1,2,3) and (4,5,6) and (7,8,9)) if it's a duplicate key (assuming column1 is the key) it will update it with the values like this:

| Insert # | VALUES(a) | VALUES(b) | VALUES(c) |
+----------+-----------+-----------+-----------+
|    1     |      1    |     2     |     3     |
|    2     |      4    |     5     |     6     |
|    3     |      7    |     8     |     9     |
+----------------------------------------------+

So VALUES(a)+VALUES(b) will be the following:

| Insert # | VALUES(a) | VALUES(b) | VALUES(c) | (a) + (b) =  X |
+----------+-----------+-----------+-----------+----------------+
|    1     |      1    |     2     |     3     | (1 + 2)   =  3 |
|    2     |      4    |     5     |     6     | (4 + 5)   =  9 |
|    3     |      7    |     8     |     9     | (7 + 8)   = 15 |
+---------------------------------------------------------------+

I hope that helps clear things up for you.

focus310

Yes, that makes sense now. Thank you for the help and explanation.

esiason14

Posted in wrong spot...sorry

bpat1434

Glad I could help!!