MYSQL - MULTIPLE SET VALUES IN ONE UPDATE STATEMENT USING 2 TABLES AS REFERENCE AND STORING VALUES IN ONE OF THOSE TABLES WITH A SPECIFIC LOGIC.
Hello people,
A problem came up by making an UPDATE. The example issue is as follows:
CURRENUSRTABLE
+------------+-------+
| ID | STATE |
+------------+-------+
| 123 | 3 |
| 456 | 3 |
| 789 | 3 |
+------------+-------+
HISTORYTABLE
+------------+------------+-----+
| ID | TRDATE | ACT |
+------------+------------+-----+
| 123 | 2013-11-01 | 5 |
| 456 | 2013-11-01 | 5 |
| 789 | 2013-11-01 | 5 |
| 123 | 2013-11-02 | 4 |
| 456 | 2013-11-02 | 4 |
| 789 | 2013-11-02 | 4 |
| 123 | 2013-11-03 | 3 |
| 456 | 2013-11-03 | 3 |
| 789 | 2013-11-03 | 3 |
+------------+------------+-----+
I'm using these variables:
@BA=3,
@DE=5,
@BL=4,
What I'm trying to do is an update on CURRENUSRTABLE.STATE using HISTORYTABLE.ACT with the following logic:
STATE value will be updated as ACT value, except when STATE value is 4 and ACT is 3, then STATE will be 5
I made this statement:
UPDATE CURRENUSRTABLE RIGHT OUTER JOIN HISTORYTABLE
ON HISTORYTABLE.ID=CURRENUSRTABLE.ID
SET CURRENUSRTABLE.STATE=
(
SELECT CASE HISTORYTABLE.ACT
WHEN @DE THEN @DE
WHEN @BL THEN @BL
WHEN @BA THEN CASE CURRENUSRTABLE.STATE
WHEN @BL THEN @DE
ELSE @BA
END
END
ORDER BY
HISTORYTABLE.TRDATE,FIELD(HISTORYTABLE.ACT,@DE,@BL,@BA)
)
WHERE
HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-01'
I'm intentionally using "RIGHT OUTER JOIN" and "HISTORYTABLE.TRDATE BETWEEN" because I'd like to change the values in CURRENUSRTABLE using a timeframe of more than one day.
If I execute this statement many times using only one day (i.e. "BETWEEN '2013-11-01' AND '2013-11-01'" and then "BETWEEN '2013-11-02' AND '2013-11-02'"... etc ) it works perfectly, but if it is executed using the dates "BETWEEN '2013-11-01' AND '2013-11-03'" the results on CURRENUSRTABLE.STATE are 3, which is wrong, it should be 5.
I think the problem relies on "CASE CURRENUSRTABLE.STATE" when uses "HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-03'", because it reads the STATE 9 times which has not been commited yet until the statement ends.
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0
Maybe the solution is very simple, but unfortunately I've not much practice on MySQL since I've worked with it less than 2 months :)
Is there any suggestions to solve this issue?
PD: MySQL version is 4.1.22, I know is very old an EOL, unfortunately I have to make these statements on this version.
Thanks!
