CODEWITHSUNDEEP
mysql
Tom Friedrichs
Tom Friedrichs

Reputation: 43

MySQL conditional update if value in same table higher than before

I am trying to update various fields in a table, thereof one under the condition that the new value is higher than the current one. I tried this:

UPDATE `table` 
SET `field1`='100', 
`field2` = IF((SELECT `field1` FROM `table` WHERE id='1')<'50','2022-10-14',`field2`)
WHERE `id`='1';

Intention: Field 1 is an amount that has been paid. Field 2 is an payment date Field 2 should only be set, in case field 1 has been updated versus like it was before. intented: Save new payment date only in case the now entered value is higher than it was before.

Problem: #1093 - The use of the target table 'invoice' to be updated is not allowed in the FROM clause.

It seems that I can not read a field in the same query from the same table. Has anyone got an idea? Thanks.

This is the original query:

UPDATE `invoice` 
SET `amount`='400',
`paid`='-50',
`paid_date`=IF((SELECT `paid` FROM `invoice` WHERE id='48')<'-50','2022-10-14'), 
`remark`='abcde' 
WHERE `id`='48';

This is how the entry currently looks like

CREATE TABLE `invoice` (
  `id` int NOT NULL,
  `created` tinyint(1) NOT NULL DEFAULT '0',
  `period` date NOT NULL COMMENT 'chose first of month for period',
  `cert_period` int NOT NULL DEFAULT '0',
  `cert_counter` int NOT NULL DEFAULT '0',
  `from_tc` int NOT NULL,
  `to_tc` int NOT NULL,
  `host` varchar(40) NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `vat_amount` decimal(10,2) NOT NULL,
  `paid` decimal(10,2) NOT NULL,
  `paid_date` date NOT NULL,
  `reminder` int NOT NULL DEFAULT '0',
  `reminder_date` datetime DEFAULT NULL,
  `reminder_blocked_until` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `remark` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `invoice` (`id`, `created`, `period`, `cert_period`, `cert_counter`, `from_tc`, `to_tc`, `host`, `invoice_date`, `due_date`, `amount`, `vat_amount`, `paid`, `paid_date`, `reminder`, `reminder_date`, `reminder_blocked_until`, `remark`) VALUES
(48, 0, '2022-01-01', 0, 5, 0, 30, 'abcde', '2022-03-06', '2022-03-14', '0.00', '0.00', '0.00', '0000-00-00', 0, NULL, '2022-10-11 11:29:38', NULL);

Upvotes: 0

Views: 61

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

Given that both the subquery and outer query are scoped to id = 1, we should be able to simplify and completely drop the subquery:

UPDATE yourTable
SET field1 = 100,
    field2 = IF(field1 < 50, '2022-10-14', field2)
WHERE id = 1;

If the outer query did not have id = 1, we could express this as an update cross join:

UPDATE yourTable t1
INNER JOIN (SELECT field1 FROM yourTable WHERE id = 1) t2
SET t1.field1 = 100,
    t1.field2 = IF(t2.field1 < 50, '2022-10-14', t1.field2)
WHERE id = 1;

Upvotes: 3

Related Questions