UPDATE Statement in MySQL Flow is not updating the value in the Table.

Hi Team,

I am trying to create a MySQL Data Flow using below SQL Transform which will have a UPDATE statement. Some How it is not updating the Column in the output table which I want to update. Can you please check what am I missing ?

======================================

CREATE Procedure GetAllDates()

BEGIN

DECLARE l_cnt INTEGER DEFAULT 1;
DECLARE l_region varchar(10);
DECLARE l_org varchar(3);
DECLARE l_ordered_item varchar(100);
DECLARE l_date DATE;

DECLARE l_group_sequence INTEGER DEFAULT 0;

DECLARE C_DATE CURSOR FOR
SELECT DISTINCT
REGION
,ORG
,ORDERED_ITEM
, DATE (DUE_DATE) DUE_DATE
FROM sd_atp_supply_demand_data
WHERE 1 = 1
AND REGION = 'TM UK'
AND ORG = 'FAR'
AND ORDERED_ITEM = 'N7608601'
AND QUANTITY <> 0
ORDER BY due_date ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_cnt = 0;

OPEN C_DATE;

get_date: LOOP

	FETCH C_DATE INTO l_region, l_org, l_ordered_item, l_date;

if l_cnt = 0
then leave get_date;
end if;

UPDATE `xxtm_atp_demand`
SET allocated_supply_transaction_id = l_group_sequence
WHERE `DUE_DATE` = l_date;

l_group_sequence = l_group_sequence + 1;

COMMIT;

END LOOP get_date;

CLOSE C_DATE;

END

===================================

Thanks,

Shrikant

Answers

  • Sorry for the unformatted MySQL Code. Please find below in a formatted one

    CREATE PROCEDURE GetAllDates ()
    
    BEGIN
    	DECLARE l_cnt INTEGER DEFAULT 1;
    	DECLARE l_region VARCHAR(10);
    	DECLARE l_org VARCHAR(3);
    	DECLARE l_ordered_item VARCHAR(100);
    	DECLARE l_date DATE;
    	DECLARE l_group_sequence INTEGER DEFAULT 0;
    
    	DECLARE C_DATE CURSOR
    	FOR
    	SELECT DISTINCT REGION
    		,ORG
    		,ORDERED_ITEM
    		,DATE (DUE_DATE) DUE_DATE
    	FROM sd_atp_supply_demand_data
    	WHERE 1 = 1
    		AND REGION = 'TM UK'
    		AND ORG = 'FAR'
    		AND ORDERED_ITEM = 'N7608601'
    		AND QUANTITY <> 0
    	ORDER BY due_date ASC;
    
    	DECLARE
    
    	CONTINUE HANDLER
    	FOR NOT FOUND
    
    	SET l_cnt = 0;
    
    	OPEN C_DATE;
    
    	get_date: LOOP
    
    	FETCH C_DATE
    	INTO l_region
    		,l_org
    		,l_ordered_item
    		,l_date;
    
    	IF l_cnt = 0 then leave get_date;END
    		IF ;
    			UPDATE `xxtm_atp_demand`
    			SET allocated_supply_transaction_id = l_group_sequence
    			WHERE `DUE_DATE` = l_date;
    
    	l_group_sequence = l_group_sequence + 1;
    
    	COMMIT;
    END
    
    LOOP get_date;
    
    CLOSE C_DATE;END
    

  • JJohnson
    JJohnson Domo Employee

    You will UPDATE with a JOIN to a table for JOINs based on a SELECT.
    https://www.mysqltutorial.org/mysql-update-join/

    UPDATE T1, T2,
    [INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
    SET T1.C2 = T2.C2,
    T2.C3 = expr
    WHERE condition

    You can also reach out to your Customer Success Manager, they will be able to get some in-depth strategic help from Domo.

  • See if this works for you:

    CREATE PROCEDURE GetAllDates ()
    BEGIN
        DECLARE l_cnt INTEGER DEFAULT 1;
        DECLARE l_region VARCHAR(10);
        DECLARE l_org VARCHAR(3);
        DECLARE l_ordered_item VARCHAR(100);
        DECLARE l_date DATE;
        DECLARE l_group_sequence INTEGER DEFAULT 0;
    
        DECLARE C_DATE CURSOR FOR
            SELECT DISTINCT REGION, ORG, ORDERED_ITEM, DATE(DUE_DATE) AS DUE_DATE
            FROM sd_atp_supply_demand_data
            WHERE REGION = 'TM UK'
            AND ORG = 'FAR'
            AND ORDERED_ITEM = 'N7608601'
            AND QUANTITY <> 0
            ORDER BY DUE_DATE ASC;
    
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_cnt = 0;
    
        OPEN C_DATE;
        get_date: LOOP
            FETCH C_DATE INTO l_region, l_org, l_ordered_item, l_date;
            IF l_cnt = 0 THEN
                LEAVE get_date;
            END IF;
            UPDATE `xxtm_atp_demand`
            SET allocated_supply_transaction_id = l_group_sequence
            WHERE `DUE_DATE` = l_date;
            SET l_group_sequence = l_group_sequence + 1;
            COMMIT;
        END LOOP get_date;
        CLOSE C_DATE;
    END
    

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thank You MarkSnodgrass for your suggestion.

    Unfortunately its not working as expected. Basically I want to update a column which I have added in SQL table created in this Data Flow "xxtm_demand".

    Seems the issue is with UPDATE statement as even below is not working irrespective of any Cursor.

    CREATE PROCEDURE GetAllDates ()
    BEGIN

    UPDATE `xxtm_demand`
    SET allocated_supply_transaction_id = 999;
    COMMIT;

    END

    "xxtm_demand" is created as SQL Table based on actual data source "sd_atp_supply_demand_data"

    select
    REGION, ORG, SUBINVENTORY, ORDERED_ITEM,SOURCE_LINE_ID,TRANSACTION_ID,date(DUE_DATE) DUE_DATE, ORDER_TYPE DEMAND_TYPE, ORDER_NUMBER DEMAND_ORDER_NUMBER, QUANTITY DEMAND_QTY
    , '' ALLOCATED_SUPPLY_TYPE
    , '' ALLOCATED_SUPPLY_ORDER_NUMBER
    , 0 ALLOCATED_SUPPLY_QTY
    , 0 ALLOCATED_SUPPLY_TRANSACTION_ID
    , 'N' PROCESS_STATUS
    , @rank:= @rank + 1 AS DEMAND_NUM
    from sd_atp_supply_demand_data
    ,(SELECT @rank:=0) b
    where 1=1
    and SOURCE_TABLE = 'MSC_DEMANDS';

    Hopefully it might help you to understand my issue.

    Thanks,

    Shrikant

  • Hi @shrikantdeshmane

    Is it a matter that it's throwing an error message or it's saying it succeeds but just doesn't update any records?

    What was the code you used to create the table xxtm_demand?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**