nodirtyrockstar Posted October 26, 2012 Share Posted October 26, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/ Share on other sites More sharing options...
nodirtyrockstar Posted October 26, 2012 Author Share Posted October 26, 2012 I thought I could use ON DUPLICATE KEY UPDATE, but that is problematic since there are multiple unique keys. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1387996 Share on other sites More sharing options...
Muddy_Funster Posted October 29, 2012 Share Posted October 29, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1388470 Share on other sites More sharing options...
fenway Posted November 1, 2012 Share Posted November 1, 2012 Solved how? Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389181 Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 By magic I guess.... Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389330 Share on other sites More sharing options...
nodirtyrockstar Posted November 2, 2012 Author Share Posted November 2, 2012 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. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389767 Share on other sites More sharing options...
PFMaBiSmAd Posted November 2, 2012 Share Posted November 2, 2012 I created another field ... You can just create a composite key using the two existing fields. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389774 Share on other sites More sharing options...
nodirtyrockstar Posted November 2, 2012 Author Share Posted November 2, 2012 Would you care to elaborate? Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389778 Share on other sites More sharing options...
PFMaBiSmAd Posted November 2, 2012 Share Posted November 2, 2012 You would add a unique key to your existing table definition - UNIQUE KEY `somename` (`products_id`,`sessions_id`) Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1389789 Share on other sites More sharing options...
nodirtyrockstar Posted November 5, 2012 Author Share Posted November 5, 2012 This helped! Thank you. Link to comment https://forums.phpfreaks.com/topic/269951-update-and-insert-help/#findComment-1390407 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.