system.db.runPrepUpdate for updating multiple rows

Ignition
1

I’m looking for some insight on the syntax for updating multiple rows within one query- Here is my current code, but this returns the error-“ERROR: operator does not exist:”

system.db.runPrepUpdate(“UPDATE rw_spt_conversion_rates SET conversion_rate = (?, ?) WHERE fkk_part_number = (?,?) AND fk_operation_code = (?,?)”, [convRate002, partNumber, “002”, convRate004, partNumber, “004”])

Thanks-

2

I’m not sure which database you are working with (my experience is with MySQL). I generally try to avoid updating multiple rows in this way due to the added complexity vs just running two update queries. But if you really want to update in a single UPDATE query, off the top of my head you could use a CASE function (and you will need to use the IN operator for the WHERE conditions instead of the equal operator):

update_query = """
    UPDATE rw_spt_conversion_rates 
    SET conversion_rate = (
        CASE 
            WHEN fkk_part_number = ? AND fk_operation_code = ? THEN ?
            WHEN fkk_part_number = ? AND fk_operation_code = ? THEN ?
            ELSE conversion_rate
        END
    )
    WHERE (fkk_part_number = ? AND fk_operation_code = ?)
        OR (fkk_part_number = ? AND fk_operation_code = ?)
# You need to uncomment this next line. It was causing weird formatting in the forum so I commented it out
# """

args = [...fill in the args...]
system.db.runPrepUpdate(update_query, args)

Hope this helps

3

That case condition won’t work reliably because the where clause will accept any combination of the part number and operation codes given, and you don’t have cases for four combinations. /:

If you absolutely must do something like this, you’ll have to use your DB flavor’s syntax for an Update involving a join with a subquery, and construct the subquery with single-row constant SELECTs combined with UNION. Blegh.

2 Likes
4

Ahh yup, good catch, I forgot to include ELSE conversion_rate in the CASE statement. Corrected in the above post. The four combinations I’m ignoring since even in separate UPDATE queries whichever is last will win (in the case statement first wins…oh well). I still don’t like it though…have to agree with the “Blegh”. I tend to lean towards INSERT...ON DUPLICATE KEY UPDATE... so that I can just upsert easily (a bit painful for properly maintaining ON UPDATE CURRENT_TIMESTAMP columns, but manageable). I didn’t want to dive into that can of worms in my post…

(Made an additional edit to the WHERE condition above to make sure we only capture rows with the proper pairs instead of mixing up matches between the pairs by using the IN operator…still ugly, just slightly less so :slight_smile: )

5

Should have mentioned this is postgres-
Thank you for the replies; much appreciated-
I will alter my code-

What is puzzling is this was my code for adding three rows and it works perfectly-
Logic suggested that UPDATE would follow the same logic…

system.db.runPrepUpdate(“INSERT INTO rw_spt_conversion_rates
(fkk_part_number, fk_operation_code, conversion_rate)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)”,
[partNumber, “002”, convRate002, partNumber, “004”, convRate004, partNumber, “006”, convRate006])

6

Well, I would think logic would follow the documentation. Multiple-row inserts is a documented syntax. No such thing for UPDATE.

7

:grinning: No wonder I couldn’t find it…
Thanks for the help- :+1: