Update a single table based on data from multiple tables in snowflake

user_3840800 0
Viewed 295
UPDATE ACCOUNTING_ENTITIES_N aen
SET aen.gmcid = (
        select
            lm.gmcid --,lm.gmlid,lm.cost_center,aen.client_name,cm.client_name,aen.customer_account
        from customer_master cm
        inner join location_master lm on  cm.gmcid =lm.gmcid
        inner join ACCOUNTING_ENTITIES_N aen on  lm.cost_center=aen.cost_center
            --and lm.cost_center='1405FC0505'
                  )
FROM ACCOUNTING_ENTITIES_N aen
JOIN location_master lm ON lm.cost_center=aen.cost_center
JOIN customer_master cm ON cm.gmcid =lm.gmcid;

SQL compilation error: duplicate alias 'AEN' --WHERE lm.cost_center='1405FC0505';

snowflake-cloud-data-platform sql
1 Answers

UNTESTED: My guess w/o any way to validate.

This should resolve the alias error: I make NO comments regarding the actual update statement working. I'm not sure what you're trying to do,

  • aen to Uaen when used in the update.
  • aen to subaen when used in a subquery

..Giving us...

UPDATE ACCOUNTING_ENTITIES_N Uaen
SET Uaen.gmcid = (
        select
            lm.gmcid --,lm.gmlid,lm.cost_center,subaen.client_name,cm.client_name,subaen.customer_account
        from customer_master cm
        inner join location_master lm on  cm.gmcid =lm.gmcid
        inner join ACCOUNTING_ENTITIES_N subaen 
           on  lm.cost_center=subaen.cost_center
            --and lm.cost_center='1405FC0505'
                  )
FROM ACCOUNTING_ENTITIES_N aen
JOIN location_master lm ON lm.cost_center=aen.cost_center
JOIN customer_master cm ON cm.gmcid =lm.gmcid;

Maybe.... you mean...

UNTESTED:

UPDATE ACCOUNTING_ENTITIES_N Uaen
   SET Uaen.gmcid = lm.gmcid
  FROM ACCOUNTING_ENTITIES_N aen
 INNER JOIN location_master lm 
    ON lm.cost_center=aen.cost_center
 INNER JOIN customer_master cm 
    ON cm.gmcid =lm.gmcid;
user_10164350
Related