'Query to update the column of second table based on newly generated id of first table

I have two tables company_types and companies_profiles. company_types_id of company_types and company_type_id of companies_profiles has one to many relationship.

Few new entries has been added recently to company_types table for platform_country_mapping_id bc82e358-919d-44f2-aa35-24416a052c21 with all the similar name what we had earlier for 8cb794b5-c37a-4c19-880d-355f65c1d33f. So now i have to update companies_profiles table's company_type_id column with newly generated company_types_id in company_types table.

company_types

company_types_id    name                        platform_country_mapping_id     
1                   name1                       8cb794b5-c37a-4c19-880d-355f65c1d33f
2                   name2                       8cb794b5-c37a-4c19-880d-355f65c1d33f
3                   name3                       8cb794b5-c37a-4c19-880d-355f65c1d33f

101                 name1                       bc82e358-919d-44f2-aa35-24416a052c21
102                 name2                       bc82e358-919d-44f2-aa35-24416a052c21
103                 name3                       bc82e358-919d-44f2-aa35-24416a052c21

companies_profiles

company_id  company_type_id             platform_id                             platform_name
149         1                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1
150         2                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1
185         3                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1

248         1                       bc82e358-919d-44f2-aa35-24416a052c21        platformName2
249         2                       bc82e358-919d-44f2-aa35-24416a052c21        platformName2
250         1                       bc82e358-919d-44f2-aa35-24416a052c21        platformName2
251         3                       bc82e358-919d-44f2-aa35-24416a052c21        platformName2

Required result of updated companies_profiles table

company_id  company_type_id             platform_id                             platform_name
149         1                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1
150         2                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1
185         3                       8cb794b5-c37a-4c19-880d-355f65c1d33f        platformName1

248         101                     bc82e358-919d-44f2-aa35-24416a052c21        platformName2
249         102                     bc82e358-919d-44f2-aa35-24416a052c21        platformName2
250         101                     bc82e358-919d-44f2-aa35-24416a052c21        platformName2
251         103                     bc82e358-919d-44f2-aa35-24416a052c21        platformName2

Below is the query what i have tried and works fine for at least one entry.

Update companies_profiles 
  set company_type_id= (Select company_types_id 
                        from company_types 
                        where name = (Select name 
                                      from company_types 
                                      where company_types_id = (Select company_type_id 
                                                                from companies_profiles 
                                                                where platform_name='platformName2' 
                                                                limit 1 offset 0)
                                     ) 
                          and platform_country_mapping_id='bc82e358-919d-44f2-aa35-24416a052c21'
                      ) 
where company_type_id = (Select company_type_id 
                         from companies_profiles 
                         where platform_name='platformName2' limit 1 offset 0) 
and platform_id='bc82e358-919d-44f2-aa35-24416a052c21'
sqlpostgresql


Solution 1:[1]

Single query for name is not possible so i wrote the seprate query for each name as below:

Update companies_profiles set company_type_id=(Select company_types_id from company_types where name= 
'name1' and platform_country_mapping_id='bc82e358-919d-44f2-aa35-24416a052c21') where company_type_id=
(Select company_types_id from company_types where name= 
'name1' and platform_country_mapping_id='8cb794b5-c37a-4c19-880d-355f65c1d33f') 
and platform_id='bc82e358-919d-44f2-aa35-24416a052c21';

Update companies_profiles set company_type_id=(Select company_types_id from company_types where name= 
'name2' and platform_country_mapping_id='bc82e358-919d-44f2-aa35-24416a052c21') where company_type_id=
(Select company_types_id from company_types where name= 
'name2' and platform_country_mapping_id='8cb794b5-c37a-4c19-880d-355f65c1d33f') 
and platform_id='bc82e358-919d-44f2-aa35-24416a052c21';

Update companies_profiles set company_type_id=(Select company_types_id from company_types where name= 
'name3' and platform_country_mapping_id='bc82e358-919d-44f2-aa35-24416a052c21') where company_type_id=
(Select company_types_id from company_types where name= 
'name3' and platform_country_mapping_id='8cb794b5-c37a-4c19-880d-355f65c1d33f') 
and platform_id='bc82e358-919d-44f2-aa35-24416a052c21';

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ambrish Mayank

Related Questions