Details about this thread
  • State Not Answered
  • Replies 3 replies
  • Subscribers 294 subscribers
  • Views 945 views
  • Users 0 members are here
Related content from around SugarClub

Add missing rows for cstm tables

sonesay inthavong

Hi everyone

I have added in a new custom field for a custom module at a later date after it was deployed. This module never had any custom fields before and now has a new one so the _cstm table is created. My issue with this is it only has rows for records that have since been updated. 

mysql> SELECT COUNT(*) FROM dvjxn_dealership_vehicle_jxn_cstm;
+----------+
| COUNT(*) |
+----------+
| 21958 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM dvjxn_dealership_vehicle_jxn;
+----------+
| COUNT(*) |
+----------+
| 265096 |
+----------+

I wish to do a mysql update query for the cstm table but it does not have all the matching rows from the main table. Is there a way I can create these rows so I can run a query to update the custom field on _cstm table?

Parents
  • 0

    TEST THIS ON A BACKUP FIRST !

    NEVER RUN SQL STATEMENTS ON PROD WITHOUT HAVING A BACKUP!

    when I'm not completely wrong this query should add a row to the custom table that have at least the id in the column id_c from the record that exists in the main table.

    insert into dvjxn_dealership_vehicle_jxn_cstm
    (dvjxn_dealership_vehicle_jxn_cstm.id_c)
    select distinct
    (dvjxn_dealership_vehicle_jxn.id)
    from dvjxn_dealership_vehicle_jxn
    where NOT EXISTS (
        select dvjxn_dealership_vehicle_jxn_cstm.id_c
        from dvjxn_dealership_vehicle_jxn_cstm
        where dvjxn_dealership_vehicle_jxn.id = dvjxn_dealership_vehicle_jxn_cstm.id_c)

    You may alter this querry, probably add empty values to additional fields but the logic should be one you need.

    Let me know if this works. I have not tested it.

    Bests

    Björn Canales Pfisterer

    Technical Support Manager

    provalida GmbH

Reply
  • 0

    TEST THIS ON A BACKUP FIRST !

    NEVER RUN SQL STATEMENTS ON PROD WITHOUT HAVING A BACKUP!

    when I'm not completely wrong this query should add a row to the custom table that have at least the id in the column id_c from the record that exists in the main table.

    insert into dvjxn_dealership_vehicle_jxn_cstm
    (dvjxn_dealership_vehicle_jxn_cstm.id_c)
    select distinct
    (dvjxn_dealership_vehicle_jxn.id)
    from dvjxn_dealership_vehicle_jxn
    where NOT EXISTS (
        select dvjxn_dealership_vehicle_jxn_cstm.id_c
        from dvjxn_dealership_vehicle_jxn_cstm
        where dvjxn_dealership_vehicle_jxn.id = dvjxn_dealership_vehicle_jxn_cstm.id_c)

    You may alter this querry, probably add empty values to additional fields but the logic should be one you need.

    Let me know if this works. I have not tested it.

    Bests

    Björn Canales Pfisterer

    Technical Support Manager

    provalida GmbH

Children
No Data