Mysql, one column value changes bases to another column in the same table
this is the table:
table_test
ID------INT
DETAIL--TEXT
VALUE---INT
STATUS--INT DEFAULT 1
as here the default is 1 for the column STATUS
and the default is 1 for the column VALUE
.
Here's an insert or update anyway ,
if column VALUE < 1 , STATUS = 2 else STATUS = 1
How to store top type script in MySQL table?
source to share
You can do this with several triggers:
delimiter //
CREATE TRIGGER table_test_update_tr BEFORE UPDATE ON table_test
FOR EACH ROW
BEGIN
IF NEW.value < 0 THEN
SET NEW.status = 2;
ELSE
SET NEW.status = 1;
END IF;
END;//
delimiter ;
delimiter //
CREATE TRIGGER table_test_insert_tr BEFORE INSERT ON table_test
FOR EACH ROW
BEGIN
IF NEW.value < 0 THEN
SET NEW.status = 2;
ELSE
SET NEW.status = 1;
END IF;
END;//
delimiter ;
EDIT:
Having said that, if status
it should always be calculated according to value
, it probably shouldn't be a column in the table - you can instead create a view to display it.
CREATE TABLE table_test (
id INT,
detail TEXT,
value INT DEFAULT 1
);
CREATE VIEW view_test AS
SELECT id,
detail,
value,
CASE value WHEN 1 THEN 1 ELSE 2 END AS status
FROM test_table;
source to share
You need to create TRIGGER in UPDATE and INSERT
DELIMITER $$
CREATE
TRIGGER `Ins_test_table` AFTER INSERT
ON `test_table`
FOR EACH ROW BEGIN
IF (VALUE < 1) THEN
SET STATUS = 2;
ELSE SET STATUS = 1;
END IF;
END$$
CREATE
TRIGGER `Update_test_table` AFTER UPDATE
ON `test_table`
FOR EACH ROW BEGIN
IF (VALUE < 1) THEN
SET STATUS = 2;
ELSE SET STATUS = 1;
END IF;
END$$
DELIMITER;
ALSO, if you can run another MYSQL query, you can END this query.
UPDATE test_table
SET STATUS = IF (VALUE < 1, 2, 1)
But its the highest load on the entire table.
source to share