MySQL combine table question

I have a table that I would like to combine into another table.  The 'properties' table that will contain the new values has an iEntityId field along with newly created and empty fields like so:
`iEntityId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`available` DATE NULL COMMENT 'Date Available',
`baths` FLOAT( 3, 2 ) NULL COMMENT 'Baths',
`bedrooms` INT( 1 ) NULL COMMENT 'Bedrooms',
`stories` INT( 1 ) NULL COMMENT 'Stories',
`sqftmain` INT( 5 ) NULL COMMENT 'Finished Sq Ft Main',
`sqftupper` INT( 5 ) NULL COMMENT 'Finished Sq Ft Upper',
`sqftlower` INT( 5 ) NULL COMMENT 'Finished Sq Ft Lower',
`sqftunfinishedlower` INT( 5 ) NULL COMMENT 'Unfinished Sq Ft Lower'

Open in new window


The table that has the values I want to move into the table above looks like so:
`tACB_EntitySearchParms` (
  `iEntityId` int(11) NOT NULL,
  `sSearchParm` varchar(50) NOT NULL,
  `sValue` varchar(255) DEFAULT NULL,
  `iValue` int(11) DEFAULT '0',
  `dValue` decimal(10,4) DEFAULT '0.0000',
  PRIMARY KEY (`iEntityId`,`sSearchParm`)
) ENGINE=InnoDB

Open in new window


The value 'tACB_EntitySearchParms' 'sSearchParm' field will has a corresponding key in the 'properties' table.  The value will depend on the type of field but will come from 'sValue', 'iValue' or 'dValue'

I would like to to something like the query below though I realize it won't work.  How can I adjust the query to get the desired results?
UPDATE `properties` p
LEFT OUTER JOIN `tACB_EntitySearchParms` s
	ON p.`iEntityId`=s.`iEntityId`
SET 
p.`available`= STR_TO_DATE(s.sValue, '%d/%c/%Y') (WHERE s.SearchParm='available') ,
p.`baths`= s.dValue (WHERE s.SearchParm='baths') ,
p.`stories`= s.iValue (WHERE s.SearchParm='stories') ,
p.`sqftmain`= s.iValue (WHERE s.SearchParm='sqftmain') ,
p.`sqftupper`= s.iValue (WHERE s.SearchParm='sqftupper') ,
p.`sqftlower`= s.iValue (WHERE s.SearchParm='sqftlower') ,
p.`sqftunfinishedlower`= s.iValue (WHERE s.SearchParm='sqftunfinishedlower')

Open in new window


Here is some sample data from tACB_EntitySearchParms table

VALUES
(2, 'baths', NULL, NULL, 3.0000),
(2, 'bedrooms', NULL, 4, 0.0000),
(2, 'stories', NULL, 1, NULL),
(17, 'baths', NULL, 0, 3.0000),
(17, 'bedrooms', NULL, 4, 0.0000),
(18, 'baths', NULL, 0, 3.0000),
(18, 'bedrooms', NULL, 4, 0.0000),
(18, 'sqftlower', NULL, 1005, 0.0000),
(18, 'sqftmain', NULL, 1636, 0.0000),
(20, 'baths', NULL, NULL, 3.0000),
(20, 'bedrooms', NULL, 4, 0.0000),
(20, 'stories', NULL, 2, NULL),
(21, 'baths', NULL, NULL, 3.0000),
(21, 'bedrooms', NULL, 4, 0.0000),
(21, 'sqftlower', NULL, 1265, 0.0000),
(21, 'sqftmain', NULL, 1465, 0.0000),
(21, 'stories', NULL, 1, NULL),
(24, 'baths', NULL, NULL, 3.0000),
(24, 'bedrooms', NULL, 5, 0.0000),
(24, 'sqftlower', NULL, 1112, 0.0000),
(24, 'sqftmain', NULL, 1502, 0.0000),
(24, 'stories', NULL, 1, NULL),
(25, 'stories', NULL, 2, NULL),
(29, 'stories', NULL, 1, NULL),
(30, 'stories', NULL, 1, NULL),
(31, 'baths', NULL, NULL, 3.0000),
(31, 'bedrooms', NULL, 4, 0.0000),
(31, 'stories', NULL, 1, NULL),
(32, 'baths', NULL, 0, 3.5000),
(32, 'bedrooms', NULL, 4, 0.0000),
(32, 'stories', NULL, 2, 0.0000),
(33, 'baths', NULL, 0, 2.0000),
(33, 'bedrooms', NULL, 3, 0.0000),
(34, 'baths', NULL, NULL, 2.0000),
(34, 'bedrooms', NULL, 3, 0.0000),
(34, 'stories', NULL, 1, NULL),
(35, 'baths', NULL, NULL, 2.0000),
(35, 'bedrooms', NULL, 3, 0.0000),
(35, 'sqftmain', NULL, 1309, 0.0000),
(35, 'sqftunfinishedlower', NULL, 1309, 0.0000),
(35, 'stories', NULL, 1, NULL),
(36, 'baths', NULL, NULL, 3.0000),
(36, 'bedrooms', NULL, 4, 0.0000),
(36, 'stories', NULL, 1, NULL)

Open in new window

LVL 1
bdhtechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Why is properties.baths float ? (and not int?)
There are no values held in sValue (only null) in the sample data.
Why is sValue varchar, if you are attempting to derive a date from it?
What happens if you ever need kitchens? (`properties` is not normalized).

Anyway, here's an update query:
UPDATE `properties` AS target
LEFT OUTER JOIN (
              SELECT
                    iEntityId
                  , max(CASE WHEN sSearchParm = 'available'           THEN STR_TO_DATE(sValue, '%d/%c/%Y') END) AS available
                  , max(CASE WHEN sSearchParm = 'baths'               THEN dValue END) AS baths
                  , max(CASE WHEN sSearchParm = 'bedrooms'            THEN dValue END) AS bedrooms
                  , max(CASE WHEN sSearchParm = 'stories'             THEN iValue END) AS stories
                  , max(CASE WHEN sSearchParm = 'sqftmain'            THEN iValue END) AS sqftmain
                  , max(CASE WHEN sSearchParm = 'sqftupper'           THEN iValue END) AS sqftupper
                  , max(CASE WHEN sSearchParm = 'sqftlower'           THEN iValue END) AS sqftlower
                  , max(CASE WHEN sSearchParm = 'sqftunfinishedlower' THEN iValue END) AS sqftunfinishedlower
              FROM tACB_EntitySearchParms
              GROUP BY
                    iEntityId
            ) AS source
	ON target.`iEntityId`= source.`iEntityId`
SET 
  target.available           = source.available
, target.baths               = source.baths
, target.bedrooms            = source.bedrooms
, target.stories             = source.stories
, target.sqftmain            = source.sqftmain
, target.sqftupper           = source.sqftupper
, target.sqftlower           = source.sqftlower
, target.sqftunfinishedlower = source.sqftunfinishedlower
;

Open in new window

see it at: http://sqlfiddle.com/#!2/b1e9a/1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bdhtechnologyAuthor Commented:
Thank you that worked perfectly!

Baths are a float because there can be a 2 1/2, 3 1/4, etc option.

It looks like I had the parameters wrong for STR_TO_DATE, it should be:
STR_TO_DATE(sValue, '%c/%d/%Y') (sample below)
I am not sure why the date parameter was stored as a VARCHAR previously.  This is a database that I am taking over that another person created.  It was actually a Microsoft SQL database that I converted to SQL, and I am attempting to clean it up and make it work better.

I certainly don't claim to be a database expert so maybe combining these tables isn't the best idea with regards to normalization, but it will make the user interface portion of the site easier to code.  You are correct if we added another column such as kitchens, then there would be a lot of wasted space in the database.  I tend to fall into the 'spreadsheet syndrome' but that is partly because they will be exporting this data to Excel so that is what we are going for (:

(79, 'available', '06/15/2011', NULL, NULL),
(79, 'baths', NULL, NULL, 3.0000),
(79, 'bedrooms', NULL, 4, NULL),
(79, 'sqftlower', NULL, 611, NULL),
(79, 'sqftmain', NULL, 1239, NULL),
(79, 'sqftunfinishedlower', NULL, 0, NULL),
(79, 'stories', NULL, 2, NULL),
(80, 'baths', NULL, NULL, 3.5000),
(80, 'bedrooms', NULL, 3, 0.0000),
(80, 'sqftlower', NULL, 600, NULL),
(80, 'sqftmain', NULL, 715, NULL),
(80, 'sqftupper', NULL, 830, NULL),
(80, 'stories', NULL, 2, 0.0000),
(81, 'baths', NULL, 0, 2.0000),
(81, 'bedrooms', NULL, 3, 0.0000),
(81, 'sqftlower', NULL, 715, 0.0000),
(81, 'stories', NULL, 2, 0.0000),
(82, 'stories', NULL, 1, NULL),
(83, 'stories', NULL, 1, NULL),
(84, 'sqftlower', NULL, 435, NULL),
(84, 'sqftmain', NULL, 1255, NULL),
(84, 'stories', NULL, 1, NULL),
(85, 'stories', NULL, 1, NULL),
(86, 'available', '06/15/2011', NULL, NULL)

Open in new window

PortletPaulEE Topic AdvisorCommented:
Regarding baths being Float:
You are updating that field from an integer field (dValue), i.e. this:

, max(CASE WHEN sSearchParm = 'baths'               THEN dValue END) AS baths

so I don't follow how you can get 2 1/2, 3 1/4. Additionally Float is a level of numeric precision you almost certainly don't need and a decimal (18,4) or similar, may be a better choice. nb: 18,4 is an arbitrary choice you can choose the level of precision needed
Regarding normalization, at some point you will regret not having normalized tables - but I cannot predict when, or exactly why, but it will happen. (e.g. you may we need to "unpivot" data to simplify calculations at some point.)

There can be some performance advantages to non-normalized tables (such as your UI example) but take care you are not going too far with this approach.

Best of luck with the project. Cheers, Paul
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.