Insert...on duplicate key update

[lurtnowski]

I’m confused on the correct way to set up this query.
I have a table

create table devices_to_be_moved (
   device_to_be_moved_id INT NOT NULL AUTO_INCREMENT,
   device_id INT NOT NULL,
   beginning_x FLOAT(5,2),
   to_beginning_slot FLOAT(4,2) NOT NULL,
   to_ending_slot FLOAT(4,2) NOT NULL,
   from_rack INT NOT NULL,
   to_rack INT NOT NULL,
   date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   user VARCHAR(50),
   notes TEXT,
   accept BOOLEAN DEFAULT 0, 
   verdict BOOLEAN,
   FOREIGN  KEY ( device_id ) REFERENCES devices ( device_id ),
   FOREIGN  KEY ( from_rack ) REFERENCES racks ( rack_id ),
   FOREIGN  KEY ( to_rack ) REFERENCES racks ( rack_id ),
   PRIMARY KEY ( device_to_be_moved_id )
); 

when I fill out a form to add an entry.

Which works as the table gets updated.
When I click the link, it takes me to

so I can review my handywork.
Lets say I screwed up and reposition the object, I submit the form and get

But when I check the table, I notice an INSERT was performed.
I think my logic is screwed up as I was hoping for an update.
The PK (move_id=5) is in the URL of the 2nd screenshot (before I m ake any corrections, but how do I alter my query to perform the UPDATE part if the PK is allready set?

Thanks

How do I make it so I can run the UPDATE if I only change three values (beginning_x, beginning_slot, ending_slot) in that record

[r937]

i’d be happy to help you, except i can’t read those screenshots – how about posting text, please

also, i notice that the only key you’ve defined in the table is the auto_increment PK – that’s never going to experience a duplicate on an insert, is it

[m_hutley]

based on the attempted query, as r937 points out, you lack a key on device_id.

For reference, the query in question is:

INSERT INTO devices_to_be_moved(device_id,beginning_x,to_beginning_slot,to_ending_slot,from_rack,to_rack)
VALUES (56,0,42,44,3,5)
ON     DUPLICATE KEY 
UPDATE beginning_x = 0, to_beginning_slot = 42, to_ending_slot = 44

For starters, you could save yourself some variable wrangling in PHP by replacing your update bit with
UPDATE beginning_x = VALUES(beginning_x), to_beginning_slot = VALUES(to_beginning_slot), to_ending_slot = VALUES(to_endling_slot)
Also… what about the to/from rack?

[lurtnowski]

Sure, thanks for your patience.

I have that table and when I run

INSERT INTO devices_to_be_moved (device_id,beginning_x,to_beginning_slot,to_ending_slot,from_rack,to_rack) VALUES (58,0, 41, 43, 3, 5) ON DUPLICATE KEY UPDATE beginning_x = 0, to_beginning_slot = 41, to_ending_slot = 43

I get the record added into the table.
But, when I look at the end result, and make a correction, I get

INSERT INTO devices_to_be_moved (device_id,beginning_x,to_beginning_slot,to_ending_slot,from_rack,to_rack) VALUES (58,0, 42, 44, 3, 5) ON DUPLICATE KEY UPDATE beginning_x = 0, to_beginning_slot = 42, to_ending_slot = 44

But another record is inserted. I was thinking is there a way to see if the PK is set, and if it is, run the UPDATE
When the PK is first set, its in the php variable

$move_id

I guess I can run an UPDATE query if that variable is set? But this query structure wouldn’t look to see if the PK is allready set?
Caan it not be done without somne php code to see if the php variable has even been set?

[r937]

@lurtnowski

But this query structure wouldn’t look to see if the PK is allready set?

yes, ON DUPLICATE KEY can be triggered by detecting a duplicate PK

alternatively, it can be triggered by detecting a duplicate UNIQUE key

you’re never going to get a duplicate on your auto_increment PK unless you specifically write your INSERT statement to mention that column and provide an actual duplicating PK value… but if you’re going to do that, why is this not an UPDATE instead?

alternatively, if you had a UNIQUE key, any attempt to do an insert for a duplicate key, would allow you to update specific values on that row

it’s kinda like “if this row exists, update these values, but if it doesn’t exist, go ahead and insert a new row with all these values”