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

shrikantdeshmane

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

shrikantdeshmane

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

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

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

shrikantdeshmane

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

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?