-2

I searched on stack and try to apply many solutions i found, but not a single is working with this one.

I have a problem with this query because ON DUPLICATE KEY UPDATE is INSERT new results instead of updating existing ones.

INSERT INTO signal_providers_relations (
    id,
    client_id, 
    provider_id, 
    active_sp, 
    subscription_sp, 
    created_by, 
    cr_datetime, 
    ea_settings_active_sp, 
    ea_settings_sp,
    up_datetime, 
    sp_risk_allocation, 
    sp_max_trades ) 
VALUES (
    '',
    '".$user_id."', 
    '".$r['provider_id']."', 
    '".$r['active_sp']."', 
    '".$r['subscription_sp']."', 
    '".$r['created_by']."', 
    '".$r['cr_datetime']."', 
    '".$r['ea_settings_active_sp']."', 
    '".$r['ea_settings_sp']."', 
    '".$r['up_datetime']."', 
    '".$r['sp_risk_allocation']."', 
    '".$r['sp_max_trades']."'
    )
ON DUPLICATE KEY
UPDATE 
    id = '".$r['id']."'
    client_id = '".$user_id."', 
    provider_id = '".$r['provider_id']."', 
    active_sp = '".$r['active_sp']."', 
    subscription_sp = '".$r['subscription_sp']."', 
    created_by = '".$r['created_by']."', 
    cr_datetime = '".$r['cr_datetime']."', 
    ea_settings_active_sp = '".$r['ea_settings_active_sp']."', 
    ea_settings_sp = '".$r['ea_settings_sp']."', 
    up_datetime = '".$r['up_datetime']."', 
    sp_risk_allocation = '".$r['sp_risk_allocation']."', 
    sp_max_trades = '".$r['sp_max_trades']."'

Here is a image with table structure

enter image description here

Mario
  • 518
  • 2
  • 19
  • 4
    On which column(s) do you have a unique index? Keep in mind, if you don't tell MySQL which columns need to be treated as unique, then `ON DUPLICATE KEY` won't work/do anything. – Tim Biegeleisen May 15 '18 at 10:33
  • 1
    Looking at those images, the **only** `UNIQUE` key you have in your table is the `PRIMARY` key on `id` ... which you're not setting - it *looks* like you should have a `UNIQUE` compound key on `client_id` and `provider_id` – CD001 May 15 '18 at 10:36
  • @CD001 even if i use id in query its still insert new records – Mario May 15 '18 at 10:42
  • Specifying an existing id in the query? – CD001 May 15 '18 at 10:43
  • @CD001 Yes, that's why i told in my question that i didn't find a solution for this – Mario May 15 '18 at 10:44
  • Could you update the question then, with the id update the query? From what you've got here currently, I'd expect new records to be inserted... – CD001 May 15 '18 at 10:46
  • 1
    You're inserting an empty string into `id` in that query ... so, there won't *be* a `DUPLICATE` key - so `ON DUPLICATE KEY` won't trigger. – CD001 May 15 '18 at 10:50
  • @CD001 thanks how i see where is a problem. – Mario May 15 '18 at 10:53
  • Possible duplicate of ["INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"](https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – Kakul Sarma May 15 '18 at 11:05

2 Answers2

0

Assuming this table holds data specific to a single client/provider relationship then your UNIQUE identifier is really a combination of the client_id and provider_id - so you could drop your current client_id and provider_id INDEXES and replace them with a UNIQUE compound index.

ALTER TABLE `signal_providers_relations` DROP INDEX `client_id`;
ALTER TABLE `signal_providers_relations` DROP INDEX `provider_id`;

ALTER TABLE `test`.`signal_providers_relations` 
    ADD UNIQUE `unique_client_provider` (`client_id`, `provider_id`);

This will give you a specific, unique key which would trigger ON DUPLICATE KEY UPDATE when you attempt to insert data for a specific client/provider combination that already exists, which is what I think you're trying to achieve.

It's quite likely you could, at this point, drop the meaningless id column and change that unique_client_provider key just created to being the PRIMARY instead - but that may have repercussions on other parts of your code.

CD001
  • 8,332
  • 3
  • 24
  • 28
-1

to avoid this use REPLACE INTO instead of INSERT INTO in your query

Mani Nagarajan
  • 52
  • 5