PHP mysqli on duplicate key update

[Joveice]

Hello, I'm trying to update my tables when it finds a duplicate.

Not quite sure how it works.

 

$mysqli->prepare('INSERT INTO status_storage_pools (pname, totalspace, totalused) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE totalspace=VALUES(totalspace), totalused=VALUES(totalused)'))

The value that will be duplicate is pname.

This query just adds a new one.

[zwirek2201]

Is pname the primary key of your table or you set the "unique" identifier on the pname column? If not, it is always going to insert a new row.

[Joveice]

18 minutes ago, zwirek2201 said:

Is pname the primary key of your table or you set the "unique" identifier on the pname column? If not, it is always going to insert a new row.

Oh, yea the id is primary, can I keep that to work as a id and change pname to primary without breaking anything? (id is not used for anything else than keeping each table unique what so ever)

[zwirek2201]

Just now, Joveice said:

Oh, yea the id is primary, can I keep that to work as a id and change pname to primary without breaking anything? (id is not used for anything else than keeping each table unique what so ever)

You can keep your primary key as it is and add "unique" identifier to the pname field (second option on the right side

) and it should work. Essentially, the "ON DUPLICATE KEY UPDATE" will update the row if you're trying to insert a row with a duplicate primary key value or with duplicate value on any column that has the "unique" identifier set.

[Joveice]

2 minutes ago, zwirek2201 said:

You can keep your primary key as it is and add "unique" identifier to the pname field (second option on the right side

[Picture]

) and it should work. Essentially, the "ON DUPLICATE KEY UPDATE" will update the row if you're trying to insert a row with a duplicate primary key value or with duplicate value on any column that has the "unique" identifier set.

Thanks!