Hi Ed,
I tried to update a list of columns:
UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE ..
but this syntax is not accepted as you probably already know.
Thanks for your very thoughtful reply. That is exactly the type of
syntax I'm after, with only one executed WHERE clause for multiple
columns. But, as you say, it doesn't work in SQLite.
I may promote [INSERT OR] REPLACE then. It is syntactically
described in the SQLite documentation but for the semantics you may
see the original MySQL doc.
http://dev.mysql.com/doc/refman/5.0/en/replace.html
It is the only way that I see to do the update with only a single
scan of the product table.
Yes, a single scan is the objective.
Hmmm, yes, I can see how REPLACE might be useful. Perhaps something
like:
REPLACE INTO t ( id, c1, c2, c3 ) SELECT id c1, c2, c3 FROM t2 WHERE
id = new.id
REPLACE, as I understand it, does rely on the id field being created
as a PRIMARY KEY, but that should be fine (and most likely already
the case).
But may be REPLACE causes troubles in combination with triggers.
Because indirectly it performs a DELETE and a new INSERT.
I guess in a trigger it would look something like:
CREATE TRIGGER trigger
AFTER UPDATE OF id
ON t
BEGIN
UPDATE t
REPLACE INTO t ( id, c1, c2, c3 ) SELECT id, c1, c2, c3 FROM t2
WHERE id = new.id
WHERE
rowid=new.rowid
;
END
I'll have to check if that's allowed.
Other suggestions should be welcome.
Yes, other suggestions would be very welcome.
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
