>>>>> Simon Slavin writes:
>>>>> On 2 Nov 2012, at 8:58am, Ivan Shmakov wrote:
>> INSERT OR IGNORE INTO "foo" ("foo")
>> VALUES (?1);
>> INSERT INTO "bar" ("foo")
>> VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));
>> Or is there a better way to ensure that the inner SELECT either
>> returns a single row, or fails?
> What do you mean by 'fails'? Returning zero rows from a SELECT is
> not failure: it's successfully reporting that there are no such rows
> in the table.
Which, as long as the task below is considered, indicates a
“contradiction” (of a kind.)
> And there may be rows in the table even if the first INSERT failed:
> the rows may have already been inserted.
Yes.
The end result for the command sequence I'm looking for is
simple:
• "foo" has a (foo → ?1) record — it doesn't matter if it was
INSERT'ed just now, or was added at some point before;
• "bar" has a (foo → id) record, where ‘id’ is the ROWID of the
aforementioned "foo" record.
AIUI, the command sequence above does just that.
> If what you mean is that some syntax error or conflict prevented the
> first INSERT from working, the correct way to do it is to look at the
> result returned from that INSERT and see whether it is SQLITE_OK.
ACK, thanks. However, I'm not really interested in the result
of the first INSERT, and, in this case, these commands may
actually be a part of a trigger (where, I believe, I cannot look
at the result returned all that easily.)
[…]
--
FSF associate member #7257
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
