When you are going to update or delete rows in a table using QMF, SPUFI or a similar tool, you probably start by writing a SELECT statement fetching the rows you want to update or delete. Then I suppose you rewrite your SELECT statement to an UPDATE or DELETE. If you use SPUFI you most likely make a copy of your SELECT and rewrite the copy. In QMF and similar tools where you only execute one SQL statement at a time it is not so easy to create a copy. Instead it is more important to have an easy way to convert from a SELECT to an UPDATE or DELETE and vice versa.

Rewriting a SELECT statement into a DELETE is extremely easy. Just replace the word SELECT with DELETE and comment out or remove the SELECT list. Most of us have probably used a * as the select list, because we know that the final product is a DELETE. And it is easy to shift back to the SELECT especially if you use SELECT *.

It is just much more cumbersome to rewrite a SELECT into an UPDATE. The syntax up until WHERE is completely different. Lately I have developed a way of writing SELECT mixed with UPDATE syntax making it reasonably easy to switch between SELECT and UPDATE in for instance QMF. Let me show you an example:

SELECT *
FROM mytable
-- SET someDateCol = current date
--   , anotherCol = 10
WHERE anImportantCol = 'xxxx'
  AND anotherCol = 5

The above statement may seem a little silly, but if you comment out the first line using --, replace FROM in the second line with UPDATE and remove the comments in the third and fourth line, then you suddenly have a readymade UPDATE statement. And it is easy to shift between UPDATE and SELECT which is an advantage if you need to change the WHERE part and you want to be sure that the changed WHERE clause will update the correct rows.