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

shrikantdeshmane
shrikantdeshmane Member
in SQL DataFlows

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

  • shrikantdeshmane
    shrikantdeshmane Member

    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.

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.
  • shrikantdeshmane
    shrikantdeshmane Member

    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

  • GrantSmith
    GrantSmith Coach

    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!**

Categories