Update Query cannot output the require result
Posted: Sat Aug 20, 2005 6:01 am
Hi Dear All,
this is my query.
-----------------------
------------------------
when i run above update query, i want to get the answer as flws
TotalHour
------------
5
10
12
16
but, i got this.
TotalHour
------------
5
5
5
5
why? pls help, sorry for long question.
this is my table script
-----------------
-----------------
feyd | Please use
this is my query.
-----------------------
Code: Select all
delete from TempHour2Code: Select all
update temphour set totalhour=0Code: Select all
Insert Into TempHour2 (Factory_Number,Style_Number,Sections,Production_Date, iHour)
Select Factory_Number,Style_Number,Sections,Production_Date As MovementDate, iHour
From TempHour
Order By Factory_Number,Style_Number,SectionsCode: Select all
UPDATE TempHour,TempHour2 SET TempHour.TotalHour = TotalHour+TempHour2.iHour
Where TempHour.Production_Date>=TempHour2.Production_Date
and TempHour.Factory_Number= TempHour2.Factory_Number
and TempHour.Style_Number= TempHour2.Style_Number
and TempHour.sections= TempHour2.sectionswhen i run above update query, i want to get the answer as flws
TotalHour
------------
5
10
12
16
but, i got this.
TotalHour
------------
5
5
5
5
why? pls help, sorry for long question.
this is my table script
-----------------
Code: Select all
DROP TABLE IF EXISTS `TempHour`;
CREATE TABLE `TempHour` (
`Production_Date` DATETIME NOT NULL,
`Factory_Number` VARCHAR(50),
`Style_Number` VARCHAR(50),
`Sections` INT NOT NULL,
`iHour` INT,
`TotalHour` INT
) TYPE=InnoDB;
#
# Dumping data for table 'TempHour'
#
LOCK TABLES `TempHour` WRITE;
INSERT INTO `TempHour` VALUES('2005-05-17', 'MG068', 'STY068', 1, 5, 0);
INSERT INTO `TempHour` VALUES('2005-05-18', 'MG068', 'STY068', 1, 5, 0);
INSERT INTO `TempHour` VALUES('2005-05-19', 'MG068', 'STY068', 1, 2, 0);
INSERT INTO `TempHour` VALUES('2005-05-20', 'MG068', 'STY068', 1, 4, 0);
UNLOCK TABLES;
ALTER TABLE `TempHour` CHANGE `Production_Date` `Production_Date` DATE NOT NULL;
#
# Table structure for table 'TempHour2'
#
DROP TABLE IF EXISTS `TempHour2`;
CREATE TABLE `TempHour2` (
`Production_Date` DATETIME NOT NULL,
`Factory_Number` VARCHAR(50),
`Style_Number` VARCHAR(50),
`Sections` INT NOT NULL,
`iHour` INT
) TYPE=InnoDB;
#
# Dumping data for table 'TempHour2'
#
LOCK TABLES `TempHour2` WRITE;
INSERT INTO `TempHour2` VALUES('2005-05-17', 'MG068', 'STY068', 1, 5);
INSERT INTO `TempHour2` VALUES('2005-05-18', 'MG068', 'STY068', 1, 5);
INSERT INTO `TempHour2` VALUES('2005-05-19', 'MG068', 'STY068', 1, 2);
INSERT INTO `TempHour2` VALUES('2005-05-20', 'MG068', 'STY068', 1, 4);
UNLOCK TABLES;
ALTER TABLE `TempHour2` CHANGE `Production_Date` `Production_Date` DATE NOT NULL;feyd | Please use
Code: Select all
andCode: Select all
tags where approriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]