MariaDB und Updates with select same table
-
Hello, I'm having some problems getting an update running. I have done similar updates some times without problems (MySql instead of MariaDB). Maybe I'm too tired today to see the problem, it was a long working day... Maybe someone can illuminate me :) Table:
CREATE TABLE ps_data.test (
Test_ID int(11) NOT NULL AUTO_INCREMENT,
Idx int(11) NOT NULL,
Grp int(11) DEFAULT NULL,
PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;Sample-Data:
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);Update commands (none working):
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1The first update command produces even a null result on the MAX() function. Strange... Best Regards, Raul
-
Hello, I'm having some problems getting an update running. I have done similar updates some times without problems (MySql instead of MariaDB). Maybe I'm too tired today to see the problem, it was a long working day... Maybe someone can illuminate me :) Table:
CREATE TABLE ps_data.test (
Test_ID int(11) NOT NULL AUTO_INCREMENT,
Idx int(11) NOT NULL,
Grp int(11) DEFAULT NULL,
PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;Sample-Data:
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);Update commands (none working):
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1The first update command produces even a null result on the MAX() function. Strange... Best Regards, Raul
Have you tried the update statement as a select just to see if it returns the expected value. I would also change select * from test to select Idx from test
Never underestimate the power of human stupidity RAH
-
Have you tried the update statement as a select just to see if it returns the expected value. I would also change select * from test to select Idx from test
Never underestimate the power of human stupidity RAH
yes, normally I would use Select Idx, but on samples I use the most simplified syntax. The bad news is that the first two examples work on MySql but not on MariaDB. The third syntax doesn't work on any DBMS, but I never used it and got it yesterday from the internet because I was desperated. I always thought that MariaDB is fully compatible to MySql, but this is unfortunatelly not true. Really bad news for those who migrated a productive system from MySql to MariaDB and are using systax like that :(