Update Query cannot output the require result

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gle4
Forum Newbie
Posts: 1
Joined: Sat Aug 20, 2005 5:45 am

Update Query cannot output the require result

Post by gle4 »

Hi Dear All,

this is my query.
-----------------------

Code: Select all

delete from TempHour2

Code: Select all

update temphour set totalhour=0

Code: 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,Sections

Code: 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.sections
------------------------

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

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

and

Code: 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]
Post Reply