PHP MySQL Insert INTO...ON Duplicate Key Update Where

Question

I need to insert when rows don't exist in the tickets table and/or update when rows do exist with 1 query.

I tried the following and it doesn't add new records and it doesn't up the existing records.

$query_upsert = mysqli_query($mysqli, 
    "INSERT INTO tickets (
        ticket_companyname,
        ticket_ordernumber,
        ticket_datetimedeliverydate,
        ticket_ritech,
        ticket_ticketstatus
    )
    SELECT 
        nextgenorder_companyname, 
        nextgenorder_ordernumber,
        nextgenorder_deliverydate,
        '$ticket_ritech',
        '$ticket_ticketstatus'
    FROM nextgenorders2 
    WHERE 
        nextgenorder_companyname LIKE CONCAT(SUBSTRING_INDEX('$nextgenorder_companyname', ' ', 1),'%') 
        AND nextgenorder_deliverydate='$nextgenorder_deliverydate'
    ON DUPLICATE KEY UPDATE,
         ticket_companyname='$ticket_companyname',
         ticket_ordernumber='$ticket_ordernumber',
         ticket_datetimedeliverydate='$ticket_datetimedeliverydate',
         ticket_ritech='$ticket_ritech',
         ticket_ticketstatus='$ticket_ticketstatus'
    WHERE
        ticket_companyname LIKE CONCAT(SUBSTRING_INDEX('$nextgenorder_companyname', ' ', 1),'%') 
        AND ticket_datetimedeliverydate='$nextgenorder_deliverydate'
    )"
);

Can you use WHERE clause in a upsert query? I have all errors on and i'm not getting any errors.

Please help.

Thanks,

WARNING: When using mysqli you should be using parameterized queries and bind_param to add any data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or data of any kind directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Mar 10, 2020 at 22:59
This query is pretty hard to read due to the verbosity. Can you trim down the number of fields present to a more minimal version of same? if you're not getting any errors, are you sure you're checking for them? Are they being logged? Try running the query "SELECT this should not work" and if you don't get an error you have to fix that first. — tadman
– tadman, Commented Mar 10, 2020 at 23:00
Get rid of the WHERE clause at the end of the query, after ON DUPLICATE KEY UPDATE .... It's not valid syntax. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE ticket_companyname LIKE CONCAT(SUBSTRING_INDEX('$nextgenorder_com' at line 58 — miken32
– miken32, Commented Mar 10, 2020 at 23:05

GMB · Accepted Answer · 2020-03-10 23:46:46Z

1

Problems and possible improvements with your query:

it has two WHERE clause: you want only one, which should appear after the SELECT clause
you are wide open to SQL injection you should use bind parameters
you don't need to repeat the parameter in the right side of the assignments: instead, you can use the VALUES syntax
Also I think that you are trying to update too many fields in the ON DUPLICATE KEY clause, I removed some of them and let those that I thought were relevant (essentially, you don't need to update the columns that come into play to check for conflicts)

Consider:

INSERT INTO tickets (
    ticket_companyname,
    ticket_ordernumber,
    ticket_datetimedeliverydate,
    ticket_ritech,
    ticket_ticketstatus
)
SELECT 
    nextgenorder_companyname, 
    nextgenorder_ordernumber,
    nextgenorder_deliverydate,
    ?,                          --> query parameter for "ticket_ritech"
    ?,                          --> ... "ticket_ticketstatus"
FROM nextgenorders2 
WHERE 
    nextgenorder_companyname 
        LIKE CONCAT(SUBSTRING_INDEX(?, ' ', 1),'%') --> ... "nextgenorder_companyname"
    AND nextgenorder_deliverydate = ?               --> ... "nextgenorder_deliverydate"
ON DUPLICATE KEY UPDATE,
    ticket_ritech = VALUES(ticket_ritech)
    ticket_ticketstatus = VALUES(ticket_ticketstatus)

edited Mar 10, 2020 at 23:46

answered Mar 10, 2020 at 23:13

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Funk Forty Niner Over a year ago

They're using mysqli_, not PDO.

GMB Over a year ago

@FunkFortyNiner: ah, yes. I changed for positional parameters. Thank you!

Kyocera Alerts Over a year ago

The insert section works but the update doesnt. If I run the query, it inserts duplicate records instead of updating the existing ones.

Kyocera Alerts Over a year ago

This worked after changing the serial number column to a unique column. Thank you for your help.

Collectives™ on Stack Overflow

PHP MySQL Insert INTO...ON Duplicate Key Update Where

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related