adding record if it doesn't already exist
right now we are first searching for email like this:
SELECT id FROM table1 WHERE email = 'firstname.lastname@example.org'
if this doesn't return an ID then:
INSERT INTO table1 (email) VALUES ('email@example.com')
instead of doing two DB calls, how do we add the email using EXISTS ?
(2) By SeverKetor on 2021-09-20 03:01:29 in reply to 1 [link] [source]
Add a unique index to table1 on email, then do
INSERT OR IGNORE INTO "table1" ("email") VALUES ('firstname.lastname@example.org');
what about if I want to insert more fields in addition to the email - like a password ?
If the INSERT OR IGNORE fails because any UNIQUE indexes would be violated, nothing changes. If the INSERT OR IGNORE does not fail, all fields defined in the command are changed.
In other words, this does what you seem to want.
Assuming that you have a unique index on table1.email, then why not use this more simple command:
INSERT OR IGNORE INTO table1(email) VALUES ('email@example.com');
Of course, if you either have no useable index then you can use a longer more inefficient method such as:
INSERT INTO table1 (email) SELECT email FROM ( SELECT 'firstname.lastname@example.org' AS email ) AS o WHERE NOT EXISTS ( SELECT * FROM table1 WHERE email == o.email );
The former is much shorter and staightforward while the latter is much longer and slower, but is standard SQL that should work unchanged on all SQL platforms since about 1985.
how do I know if I inserted the record or ignored it ?
You could add a RETURNING clause at the end.
Why ever would you care?
If the record did not exist before the insert, then it was inserted; and if it already existed before the insert, then it was not inserted -- the net result in any case, where no error is raised to the application when the statement is executed, is that after execution the record will exist.
What ever else could you possibly need and for what conceivable reason?
Not sure if you are asking this tongue-in-cheek or not.
But the answer to this is very simple, from my experience: Drum roll...
To insert child rows, using the
RETURNING's surrogate key of the parent row, which may pre-exist, as a foreign key.
PS: Didn't read the whole thread, so maybe I'm off-topic :)
There may be an operational need for such information.
If someone asked you, during an audit, "when was this record added?", "I don't know" may not be an acceptable answer.
Well, that is a design flaw (failure to store needed information) and has nothing whatsoever to do with the question asked. Unless the inserted record contains a timestamp of when it was inserted, there is no way to tell when the record was inserted. Playing with pseudokeys will not be helpful in this regard. It also does not really matter what some excrement-head thinks "after the fact" -- they can either accept the answer "I don't know" (which by the way is an incorrect answer -- the correct answer would be "that information was not recorded").
And going back to the post to which you replied, it only appears to the uneducated that there is a need to know the pseudokey for the row. It is actually not needed for anything at all.
create table names ( name_id integer primary key, name text not null collate nocase unique ); create table houses ( house_id integer primary key, address text not null collate nocase unique ); create table peasants ( id integer primary key, name_id integer references names, house_id integer references houses ); create view persons as select peasants.id, name, address from peasants, names, houses where (peasants.name_id is null or peasants.name_id == names.name_id) and (peasants.house_id is null or peasants.house_id == houses.house_id); create trigger ins_person instead of insert into persons begin select raise(ABORT, 'Name cannot be NULL') where new.name is null; insert or ignore into names (name) values (new.name); insert or ignore into houses (address) select new.address where new.address is not null; insert into peasants (name_id, house_id) select (select name_id from names where name == new.name), (select house_id from houses where address == new.address); end;
Seems to me that there is no need to EVER deal with the pseudokeys. So I can see why someone might think they need to know the pseudokey, but they are incorrect.