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(at)illuminatedcomputing(dot)com