Re: Conditional INSERT

On 3/15/19 10:55 AM, basti wrote:
> I want to insert data into table only if condition is true.
> For example:
> 
> INSERT into  mytable (domainid, hostname, txtdata)
>    VALUES (100,'_acme.challenge.example', 'somedata');
> 
> The insert should only be done if Hostname like %_acme.challenge%.

I would use `INSERT INTO ... SELECT` for this, instead of `INSERT INTO 
... VALUES`. For example:

     INSERT INTO mytable (domainid, hostname, txtdata)
     SELECT 100, '_acme.challenge.example', 'somedata'
     WHERE '_acme.challenge.example' LIKE '%_acme.challenge%'
     ;

(Presumably in the real code the hostname is parameterized so this isn't 
quite as pointless as it looks. :-)

If you are inserting a lot of rows at once you could also SELECT from a 
VALUES list:

     INSERT INTO mytable (domainid, hostname, txtdata)
     SELECT d, h, t
     FROM (VALUES
       (100, '_acme.challenge.example', 'somedata'),
       (200, 'bar.example.com', 'somedata'),
       (300, 'foo.example.com', 'somedata'),
       (400, '_acme.challenge.example', 'somedata')
     ) x(d, h, t)
     WHERE h LIKE '%_acme.challenge%'
     ;

I hope that helps!

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com