48689

Workaround solution to do a “INSERT…ON DUPLICATE KEY UPDATE” with null value on unique key

Question:

To begin with, here is my code structure and what I want to achieve :

I have this 3 tables :

+----------------------+ | CONFIG_CAMPAIGN | +----------------------+ | - id_config | | // other fields | +----------------------+ +----------------------+ | SLOT_CONFIG | +----------------------+ | - id_slot_config | | - id_config | | - id_slot_pb | +----------------------+ +----------------------+ | SLOT_PLACE_BOX | +----------------------+ | - id_slot_pb | | - id_place_box | | - date | | - hour | | - slot_available | +----------------------+

So one "slot_place_box" can have multiple "config" and one "config" can be on multiple "slot_place_box". I have some trigger on "slot_config" to update the field "slot_available" according to the config.

Now I have a PHP where I get some FORM data and build a SQL request which look like this (when I have my problem) :

INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-10', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box; SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-10' AND SPB.hour IS NULL); INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id); INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-11', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box; SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-11' AND SPB.hour IS NULL); INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id); INSERT INTO media.slot_place_box (id_place_box, date, hour)VALUES (32, '2017-12-12', NULL)ON DUPLICATE KEY UPDATE id_place_box = id_place_box; SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = 32 AND SPB.date = '2017-12-12' AND SPB.hour IS NULL); INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (125, @id); // and so on

The idea is to create a new "slot_place_box" + "slot_config" if I have no duplicate according to the new config I create before in my code

OR

If I already have a "slot_place_box" with same "id_place_box + date + hour" I only INSERT a new "slot_config" with the new_config id + the "slot_place_box" I already have.

<em>I choose the "INSERT...ON DUPLICATE KEY /do nothing/" because I need the id of the row I INSERT / I already have.</em>

I have an UNIQUE index key in "slot_place_box" for the fields "id_place_box + date + hour". My problem begin because "hour" can be an hour ("12:00:00" for example) but can be NULL too (= for me, when NULL it means "all day" of the "date"). When hour is NULL, my UNIQUE index doesn't work so the INSERT happens and I have duplicate in database.

<strong>So how can I make this "INSERT...ON DUPLICATE KEY UPDATE" works with an UNIQUE index with NULL value?</strong>

I can't use this solution (<a href="https://stackoverflow.com/questions/42064759/how-to-do-unique-constraint-works-with-null-value-in-mysql/42291845#42291845" rel="nofollow">here</a>) because I use MySQl 5.6 so I will try to make a trigger as someone suggest me in my last question (<a href="https://stackoverflow.com/questions/47659336/insert-on-duplicate-key-update-id-id-still-inserts-on-duplicate/47670913?noredirect=1#comment82307247_47670913" rel="nofollow">here</a>).

I'm beginner in SQL so <strong>I'd like some help please to achieve this trigger please</strong>. The idea, if I understand well, is to create some "virtual" column with the trigger and add some value in it if "hour" is null + add this virtual column in my UNIQUE index so the ON DUPLICATE KEY will works.

So my questions are :

<ul><li>How can I create this "virtual column" in a trigger?</li> <li>How can I use this "virtual column" for my UNIQUE index if I need the "hour" value when it's not NULL?</li> <li>Do you have a better idea? </li> </ul>

Answer1:

I will anwser myself but if you guys have other solution I'm still interested to heard it. This solution works for me because I'm working on a new project so it don't really impact the code I already wrote.

To workaround this problem, I add a new column to my "slot_place_box" table :

+----------------------+ | SLOT_PLACE_BOX | +----------------------+ | - id_slot_pb | | - id_place_box | | - date | | - hour | | - slot_available | | - virtual_hour | <- new field +----------------------+

This new field is a VARCHAR(10) equal to "hour" if not NULL (ex: "12:00:00") or equal to "1" by default or when "hour" is NULL.

Then I change my UNIQUE index on this table for "id_place_box + date + virtual_hour" so I will never get NULL value. Then I UPDATE the data I already have in my table :

UPDATE slot_place_box SET (virtual_hour = IF(hour is NULL, '1', hour); // Without the safe UPDATE (I use MySQL WorkBench)

I change my PHP loop to build my request with the new column:

foreach($SPB_data_array as $index => $SPB_data) { $current_id_pb = $SPB_data['id_pb']; $current_date = $SPB_data['date']; if (!empty($SPB_data['hour'])) { $current_hour = $SPB_data['hour']; $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour, virtual_hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', \''. $current_hour .'\', \''. $current_hour .'\') ON DUPLICATE KEY UPDATE id_place_box=id_place_box; SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour = \''. $current_hour .'\'); INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id); '; } else { $sql_slot_pb .= 'INSERT INTO media.slot_place_box (id_place_box, date, hour) VALUES ('. $current_id_pb .', \''. $current_date .'\', NULL) ON DUPLICATE KEY UPDATE id_place_box=id_place_box; SET @id = (SELECT SPB.id_slot_pb FROM media.slot_place_box as SPB WHERE SPB.id_place_box = '. $current_id_pb .' AND SPB.date = \''. $current_date .'\' AND SPB.hour IS NULL); INSERT INTO media.slot_config (id_config, id_slot_pb) VALUES (:new_config_id, @id); '; } }

I just added the fourth column with the "hour" value when "hour" is NOT NULL, by default "virtual_hour" is "1" so no need to add it in this case.

I don't know if it's the best solution for BIG project with lot of data, but in my case it was the easiest way to resolve my problem.

Recommend

  • Merge list of objects into consistent list based on common matching attribute in Python
  • Insert records if not exist SQL Server 2005
  • Unique Permutations - with exceptions
  • Cloud Code function running twice
  • pyodbc doesn't report sql server error
  • Why value captured by reference in lambda is broken? [duplicate]
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Symfony2: How to get request parameter
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • How to delete a row from a dynamic generate table using jquery?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • log4net write single file for each call to log.info
  • Getting error when using KSoap library to consume .NET web services
  • How to set the response of a form post action to a iframe source?
  • How do you join a server to an Active Directory (domain)?
  • Understanding cpu registers
  • Why joiner is not used after Sequence generator or Update statergy
  • Change div Background jquery
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • Qt: Run a script BEFORE make
  • LevelDB C iterator
  • Add sale price programmatically to product variations
  • Recursive/Hierarchical Query Using Postgres
  • reshape alternating columns in less time and using less memory
  • How can i traverse a binary tree from right to left in java?
  • UserPrincipal.Current returns apppool on IIS
  • Converting MP3 duration time