MySQL – Sub-query Example, Multiple sub-query, DB De-Normalization, Data Duplication, Data Redundancy
Example of:
- Data Redundancy
- Data Duplication
- DB De-Normalization
- Multiple sub-query
MySQL – Sub-query Example, Multiple sub-query, DB De-Normalization, Data Duplication, Data Redundancy
This post is to show the power of single query which can complete the records by matching and comparing the reference keys from other tables. It is quite obvious to consider it as a fun but it is need of time as well. Suppose you have shortage of time and no time to code in PHP to fetch records from DB and then update the single table. Sub-queries and related tricks can save our time and the server resources. Don’t ignore the ORDER BY and GROUP BY options if you can use it for your convenience.
UPDATE `deals_cities` c SET c.cities_states_name=(SELECT s.states_name FROM deals_states s WHERE s.states_id=c.states_id LIMIT 1),c.cities_states_code=(SELECT s.states_code FROM deals_states s WHERE s.states_id=c.states_id LIMIT 1)
UPDATE deals_states s SET s.states_country_name=(SELECT cn.countries_name FROM deals_countries cn WHERE s.countries_id=cn.countries_id LIMIT 1),s.states_country_code=(SELECT cn.countries_code FROM deals_countries cn WHERE s.countries_id=cn.countries_id LIMIT 1)
UPDATE `deals_cities` c SET c.city_country_name=(SELECT s.states_country_name FROM deals_states s WHERE s.states_id=c.states_id LIMIT 1),c.city_country_code=(SELECT s.states_country_code FROM deals_states s WHERE s.states_id=c.states_id LIMIT 1)
UPDATE deals_cities d SET d.cities_states_code = (SELECT c.state FROM cities c WHERE c.city = d.cities_name LIMIT 1) WHERE d.city_country_code = 'CA' AND d.cities_states_code =' '
Last updated: March 19, 2014
