mysql add to existing row where part of number equals part of number.

[kathygriffin]

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.. \/

 

table1

id           updated_stuff
12-444-22    yippe
2223-44-1    bob
323-222-11   harry




table2

id           junkone  junktwo
12-444-22    yippe     skippe
2223-44-1    bob       bobd
323-222-11   harry     pointer




table1- after its done.

id           updated stuff
12-444-22    yippe skippe
2223-44-1    bob bobd
323-222-11   harry pointer

 

 

[Barand]

try

UPDATE table1 t1
    INNER JOIN table2 t2 USING (id)
    SET t1.updated_stuff = CONCAT(t1.updated_stuff, ' ', t2.junktwo)

[kathygriffin]

 

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 */;

[kathygriffin]

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

Edited February 14, 2014 by kathygriffin

[Barand]

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?

[kathygriffin]

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

[Barand]

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?

Edited February 14, 2014 by Barand

[kathygriffin]

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.

[Barand]

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.

[kathygriffin]

haha I have made recommendations. Its in the plans from what I have been told. I am very excited in a way.