kathygriffin Posted February 14, 2014 Share Posted February 14, 2014 I am trying to query out a part of a number where another part of a number equals, and then add some data to a row with exisitng data in it. I know how to use something like select concat('-',substring_index(s,'-',-2)) x from wow; But I can't seem to figure out how to match up part of the number. I understand inner join for the most part. Any pointers or suggestions or if anyone has a better idea on how to do this I would greatly appreicate it. However I can't seem to match it up. This what I tried, along with alot of other stuff.. update table1 WHERE concat('-',substring_index(s,'-',-2)) id from table1 = concat ('-',substring_index(s,'-',-2)) id from table2 SET table1.updated_stuff=table1.updated_stuff + junktwo from table2 What it looks like and what my goals are.. \/ table1id updated_stuff12-444-22 yippe2223-44-1 bob323-222-11 harrytable2id junkone junktwo12-444-22 yippe skippe2223-44-1 bob bobd323-222-11 harry pointertable1- after its done.id updated stuff12-444-22 yippe skippe2223-44-1 bob bobd323-222-11 harry pointer Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/ Share on other sites More sharing options...
Barand Posted February 14, 2014 Share Posted February 14, 2014 try UPDATE table1 t1 INNER JOIN table2 t2 USING (id) SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo) Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468946 Share on other sites More sharing options...
kathygriffin Posted February 14, 2014 Author Share Posted February 14, 2014 try UPDATE table1 t1 INNER JOIN table2 t2 USING (id) SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo) Thank you for pointing mein a closer direction. However its not matching on part of the part number. Thats why I had the substring in there.. table1 -- phpMyAdmin SQL Dump -- version 4.0.4.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Feb 14, 2014 at 07:37 PM -- Server version: 5.5.32 -- PHP Version: 5.4.19 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `crossdata1` -- CREATE TABLE IF NOT EXISTS `crossdata1` ( `id` varchar(255) NOT NULL, `updated_stuff` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `crossdata1` -- INSERT INTO `crossdata1` (`id`, `updated_stuff`) VALUES ('12-444-22', ' skippe skippe yippe yippe yippe'), ('2223-44-1', ' bobd bobd bob bob bob'), ('323-222-11', ' pointer pointer harry harry harry'), ('47-4000-333', ''), ('222-11', ''), ('222-11', ''), ('12-444-00', ''), ('13-444-01', ''), ('33-222-11', ''), ('500-222-11', ''); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; table2 -- phpMyAdmin SQL Dump -- version 4.0.4.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Feb 14, 2014 at 07:39 PM -- Server version: 5.5.32 -- PHP Version: 5.4.19 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `cross_pull` -- CREATE TABLE IF NOT EXISTS `cross_pull` ( `id` varchar(255) NOT NULL, `junkone` varchar(255) NOT NULL, `junktwo` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `cross_pull` -- INSERT INTO `cross_pull` (`id`, `junkone`, `junktwo`) VALUES ('12-444-22', 'yippe', 'skippe'), ('2223-44-1 ', 'bob', 'bobd'), ('323-222-11', 'harry', 'pointer'), ('48383-3343-332', 'barry', 'not'); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468949 Share on other sites More sharing options...
kathygriffin Posted February 14, 2014 Author Share Posted February 14, 2014 Sorry I messed up on part of what I told you... I need it to match on part of the part number. I'm thinking something like this sort of UPDATE table1 t1 INNER JOIN table2 t2 USING (concat('-',substring_index(s,'-',-2))id) SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo) For example table2 junktwo 993-44444= something 334-000= yes 003-344= maybe Once you run the query..... table1 updated_stuff 443-993-44444 existing data something 7748-334-000 existing data yes 348-003-344 existing data maybe Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468951 Share on other sites More sharing options...
Barand Posted February 14, 2014 Share Posted February 14, 2014 This definitely an object lesson in why you shouldn't create compound codes where different sections of the code are essentially sub-fields. Create them as separate fields and concatenate on output. Also, the object of this exercise seems to be de-normalization of your data? Those comments aside, which part/s of the code need to match? Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468953 Share on other sites More sharing options...
kathygriffin Posted February 14, 2014 Author Share Posted February 14, 2014 Trust me it wasn't my choice to do it that way, so now I'm stuck with what I have now. some are the first 2 some are the last 3. I can figure out that part.. As for the concat part thats what I need help with. You can do under a select. For example http://sqlfiddle.com/#!2/60ccb/1 Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468954 Share on other sites More sharing options...
Barand Posted February 14, 2014 Share Posted February 14, 2014 This one will append junktwo to updated_stuff where the last two sections of the code are matching UPDATE crossdata1 t1 INNER JOIN cross_pull t2 ON SUBSTRING_INDEX(t1.id,'-',-2) = SUBSTRING_INDEX(t2.id, '-', -2) SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo) PS Why are you denormalizing the data? Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468957 Share on other sites More sharing options...
kathygriffin Posted February 14, 2014 Author Share Posted February 14, 2014 This one will append junktwo to updated_stuff where the last two sections of the code are matching UPDATE crossdata1 t1 INNER JOIN cross_pull t2 ON SUBSTRING_INDEX(t1.id,'-',-2) = SUBSTRING_INDEX(t2.id, '-', -2) SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo) PS Why are you denormalizing the data? wow thank you so much you are fantastic. Its a long story... Pretty much it comes down to where they have certain things broken up then it comes down to descriptions where they are broken up. However there is alot of other stuff that should have been broken up that wasn't. And then you have 10 different ids for one item. So I am looking to build the description from there. Then tear it down. Pretty much how everything was started. It was never planned out to be this big. So some databases reall grew with some parts and other parts where built where they where really broken down. So now I am down to my mixing and matching. Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468959 Share on other sites More sharing options...
Barand Posted February 14, 2014 Share Posted February 14, 2014 I would recommend that instead of patching you take a step back and do a thorough data analysis exercise and construct a correct, working, normalized data model (with sensible code structure ). It may sound like hard work but it pays off down the line by vastly simplifying the processing required. Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468962 Share on other sites More sharing options...
kathygriffin Posted February 14, 2014 Author Share Posted February 14, 2014 haha I have made recommendations. Its in the plans from what I have been told. I am very excited in a way. Link to comment https://forums.phpfreaks.com/topic/286205-mysql-add-to-existing-row-where-part-of-number-equals-part-of-number/#findComment-1468964 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.