Update And Insert Help

[nodirtyrockstar]

I would like to update a table or insert if a specific combination of indices do not already exist. The table is very simple.

 

CREATE TABLE `sessProdLnk` ( `products_id` varchar(20) NOT NULL, `sessions_id` varchar(255) NOT NULL, `qty` int(6) NOT NULL, KEY `products_id` (`products_id`), KEY `sessions_id` (`sessions_id`) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

So, I would like to search for a match between my data, and any row in sessProdLnk where both the sessions_id and products_id match. Then I would like to update any entries in qty where they match, and insert a new row for any data pairs that do not have matches.

 

I know some of this comparison may have to be done programmatically in the php script, but I am still getting to know MYSQL and was wondering how much of the work it will do for me given these parameters.

Edited October 26, 2012 by nodirtyrockstar

[nodirtyrockstar]

I thought I could use ON DUPLICATE KEY UPDATE, but that is problematic since there are multiple unique keys.

Edited October 26, 2012 by nodirtyrockstar

[Muddy_Funster]

have you looked at triggers at all? I'm not really clear on exactly what you are looking to do, but from the sound of it a trigger might be what your looking for.

[fenway]

Solved how?

[Muddy_Funster]

By magic I guess....

[nodirtyrockstar]

I actually reexamined the architecture of the database. Essentially I had two fields that were not unique, but what would be unique is the combination of the two fields. I created another field which is a combination of the session ID and product ID. This creates a singular unique key and allows me to use the ON DUPLICATE KEY UPDATE command.

[PFMaBiSmAd]

I created another field ...

 

You can just create a composite key using the two existing fields.

[nodirtyrockstar]

Would you care to elaborate?

[PFMaBiSmAd]

You would add a unique key to your existing table definition -

 

UNIQUE KEY `somename` (`products_id`,`sessions_id`)

[nodirtyrockstar]

This helped! Thank you.