I have these tables:
CREATE TABLE stack
(
ProjectID INTEGER NOT NULL
StatusID INTEGER NOT NULL
ItemNumber INTEGER NOT NULL
Stamp DATETIME NOT NULL
);
CREATE TABLE queue
(
ProjectID INTEGER NOT NULL
StatusID INTEGER NOT NULL
ItemNumber INTEGER NOT NULL
Stamp DATETIME
Wait BIT NOT NULL
);
And these rows (date is DD-MM-YYYY hh:mm:ss):
INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10,
'28-03-2003 20:00:00');
INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10,
'28-03-2003 20:15:00');
INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 3, 10,
'28-03-2003 20:30:00');
INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 2, 10,
'28-03-2003 17:00:00');
INSERT INTO stack (ProjectID, StatusID, ItemNumber, Stamp) VALUES(1, 1, 10,
'28-03-2003 19:15:00');
INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 3, 10,
'28-03-2003 19:15:00', 0);
INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 2, 10,
'28-03-2003 17:00:00', 0);
INSERT INTO queue (ProjectID, StatusID, ItemNumber, Stamp, Wait) VALUES(1, 1, 10,
NULL, 0);
Now I wan't to UPDATE "queue" so it's stamp is set to the nearest higher stamp value
in "stack" where ProjectID, StatusID and ItemNumber is equal to "stack" values. If
stamp in "queue" is NULL then this should be set to the MIN(stamp) value in "stack,
based on the same criterias as before. Then wait should also be 1, for above
criterias, but that's not that important now...
The result rows in "queue" should look like this after doing the UPDATE:
ProjectID StatusID ItemNumber Stamp Wait
1 3 10 28-03-2003 20:30:00 1
1 2 10 28-03-2003 17:00:00 0
1 1 10 28-03-2003 19:15:00 1
I've tried with this query (this is only for StatusID=3, but imagine this run for
StatusID 1,2,3):
UPDATE queue
SET Wait = 1, stamp = S.stamp
FROM stack S
WHERE queue.ProjectID = S.ProjectID
AND queue.ItemNumber = S.ItemNumber
AND queue.StatusID = S.StatusID
AND queue.Stamp < S.Stamp
AND queue.ProjectID = 1
AND queue.ItemNumber = 10
AND queue.Wait = 0
AND queue.StatusID = 3
Plz help me - I don't know how to solve this one...
/Jakob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
