Update A Record if it exists, else do nothing, including short-cicruit

Question

What I want to do is something that has the following logic:

IF EXISTS (SELECT * FROM people WHERE ID = 168)
THEN UPDATE people SET calculated_value = complex_queries_and_calculations
WHERE ID = 168

.., so to update a field of a given record if that record contains a given data, and else do nothing. To generate the data which would be used for the update, I need to query other tables for values and make some calculations. I want to avoid these queries + calculations, if there's actually nothing to update. And in this case, simply do nothing. Hence, I guess that putting for example an EXIST clause inside a WHERE clause of the UPDATE statement would end in many queries and calculations made in vain.

How can I only UPDATE conditionally and else do nothing, and make sure that all the queries + calculations needed to calculate the value used for the update are only made if the update is needed? And then, in the end, only do the update if complex_queries_and_calculations is not NULL?

My best solution so far uses a Common Table Expression (WITH clause), which makes it impossible to short-circuit. Anyway, such that you can understand the logic I'm trying to achieve, I'm showing what I've been trying so far (without success; code below is not working and I don't know why..):

-- complex queries and calculations; return result as t.result
WITH t AS(complex queries and calculations)
UPDATE target_table
SET
CASE
WHEN t.result IS NOT NULL
THEN target_table.target_column = t.result WHERE target_table.PK = 180
END;

UPDATES (Still saying syntax error, still not working)

WITH t AS(complex_queries_and_calculations AS stamp)
UPDATE target_table
SET target_column =
CASE
WHEN t.stamp IS NULL
THEN target_column
ELSE t.stamp
END
WHERE ID = 168;

Not even this is working (still reporting syntax error on UPDATE line):

WITH t AS(complex_queries_and_calculations AS stamp)
UPDATE target_table
SET target_column = target_column
WHERE ID = 168;

(eventual better approaches which avoid redundant target_column = target_column updates welcome)

With select it works, so I'm totally not understanding the syntax error #1064 it returns for my update query:

WITH t AS(complex_queries_and_calculations AS stamp)
SELECT
CASE
WHEN t.stamp IS NULL
THEN "Error!"
ELSE t.stamp
END
FROM t;

ADDITIONAL INFO

It seems like MariaDB actually does not support CTEs with UPDATE statements; correct me if I'm wrong... So I tried the following:

UPDATE people AS p
INNER JOIN (queries_and_calculations AS result) t
ON p.ID <> t.result -- just to join
SET p.target_column = t.result
WHERE p.ID = 168
AND t.result IS NOT NULL;

and now it's saying:

#4078 - Illegal parameter data types varchar and row for operation '='

Answer 1

Simply do the UPDATE. If there is no row with that ID, it will do nothing. This will probably be no slower than testing first.

Ditto for DELETE when the row might not exist.

"Upsert"/"IODKU" -- INSERT ... ON DUPLICATE KEY UPDATE ... is useful when you want to modify some columns when the row exists (according to some unique column), or add a new row (when it does not exist). This is better than doing a SELECT first.

Think of it this way... A big part of the UPDATE is

• opening the table,
• locating the block in the table that needs to be modified
• loading that block into the cache ("buffer_pool")

All of that is needed for both your SELECT and UPDATE (yeah, redundantly). The UPDATE continues with:

• If the row does not exist, exit.
• Modify the row, and flag the block as "dirty".
• In the background, the block will eventually be flushed to disk.

(I left out details about transactional integrity ("ACID"), etc.)

Even in the worst case, the whole task (for a single row) takes under 10 milliseconds. In the best case, it takes under 1ms and can be done somewhat in parallel with certain other activities.

Answer 2

There is no IF in SQL, since it is not needed:

---

UPDATE people p
SET calculated_value = c.val
FROM (
        SELECT ID, val
        FROM
        ... complex_queries_and_calculations
        ) c
WHERE c.ID = p.ID
AND ID = 168
AND v.val <> i.val -- maybe add this to avoid idempotent updates. Beware of NULLs, though!
        ;

~

Answer 3

GOT IT, The following query works to do exactly what I wanted in Mariadb :

UPDATE target_table

LEFT JOIN (complex_queries_and_calculations_to_get_update_value AS update_value) t

ON target_table.ID <> t.update_value -- serves just to have update value in memory, 
-- because it needs to be accessed twice to create the updated column value 
-- on update, sort of a workaround for CTE + UPDATE in MariaDB

SET target_column = JSON_ARRAY( FORMAT_UPDATE_VALUE(t.update_value),
                    FORMAT_2_UPDATE_VALUE(t.update_value) )

WHERE ID = 128 AND t.update_value IS NOT NULL;

If the record does not exist, the query takes about 0.0006 secs to execute, without doing anything to the table. If it does exits, it takes 0.0014 secs to execute, while updating the targeted record accordingly. So, it indeed seems to work and resources are saved if the targeted record is not found in target_table. Great thanks to all who helped!