update query on temporary table issue - mysql

Question

update query does not update the values into temporary table in mysql.

DECLARE _defaultDateTime DATETIME;
DECLARE _resourceTypeId CHAR(36);
DECLARE _billedUsageHrs DECIMAL(15,6);
DECLARE _unbilledusageHrs DECIMAL(15,6);
DECLARE _billedCost DECIMAL(15,6);
DECLARE _unBilledCost DECIMAL(15,6);
DECLARE _resourceIdentifier CHAR(36);

DROP TABLE IF EXISTS _usageTable;

CREATE TEMPORARY TABLE _usageTable(resourceinstanceId CHAR(36),billedusageinHrs DECIMAL(15,6), billedusageCost DECIMAL(15,6),                       unBilledusageinHrs DECIMAL(15,6), unbilledusageCost DECIMAL(15,6)) ENGINE=MEMORY;


UPDATE  _usageTable SET resourceinstanceId = 'a17b5e49-000c-11e3-8bfa-842b2bac06e5' WHERE 1 = 1;  

SELECT resourceinstanceId, billedusageinHrs, billedusageCost, unBilledusageinHrs, unbilledusageCost FROM _usageTable;

The above stored procedure returns empty row

Well, when there's no data in the table, there's nothing to update. Simple as that. — fancyPants, Aug 08 '13 at 09:37
no. but when i tried insert the values to temp table it works. update doesnt. — Prem, Aug 08 '13 at 09:37
Explain that. And show the whole procedure. How do you think we can help you without necessary info? — fancyPants, Aug 08 '13 at 09:55

Sylvain Leroux · Answer 1 · 2013-08-08T10:21:06.337

CREATE TEMPORARY TABLE _usageTable(resourceinstanceId CHAR(36),
                                   billedusageinHrs DECIMAL(15,6), 
                                   billedusageCost DECIMAL(15,6),
                                   unBilledusageinHrs DECIMAL(15,6), 
                                   unbilledusageCost DECIMAL(15,6)) ENGINE=MEMORY;


UPDATE  _usageTable SET resourceinstanceId = 'a17b5e49-000c-11e3-8bfa-842b2bac06e5' WHERE 1 = 1;

UPDATE will update existing rows.

As far as I can see, you just created the table. So it is empty. There is simply no row to update.

Perhaps are you looking for INSERT instead of UPDATE ? Or maybe you example does not put emphasis on the real problem?

update query on temporary table issue - mysql

1 Answers1