MySQL rejects INSERT INTO if SELECT returns nothing

Question

I have a table links with the columns id, url, url_hash and parent_id (and many more without relevance for my question). I have 2 variables in my code, $url and $parentUrl which may refers to an existing row in links table or not. So I want to set parent_id to the id of the first link whose url matches $parentUrl or to set it to 0 or null if there is no parent.

INSERT INTO `links` (`url`, `url_hash`, `parent_id`)
SELECT               $url,  MD5(url),   `id`
FROM `links`
WHERE `url` = $parentUrl
LIMIT 1

But this fails if the select statement returns 0 rows. I'd like my query to just insert a new row based on the const values (url, url_hash).

INSERT INTO `links` (`url`, `url_hash`, `parent_id`)
VALUES              ($url,  MD5(url), ((SELECT `id` FROM `links` WHERE `url`=$parentUrl LIMIT 1))

Tried this one too, but this seems to fail with this error:

You can't specify target table 'links' for update in FROM clause

I do all this to avoid my current, working solution that uses two queries. Is it even possible in one query?

Answer 1

When you need a row -- even if there are no matches -- think aggregation. In your case:

INSERT INTO `links` (`url`, `url_hash`, `parent_id`)
    SELECT $url, MD5($url), max(id)
    FROM (SELECT id
          FROM `links`
          WHERE `url` = $parentUrl
          LIMIT 1
         ) t;

This will generate one row. I'm not sure what "first" means in this context. This will be an arbitrary row. You need an order by to get one in a particular order.

I am also guessing that the expression MD5(url) should really be MD5($url).

EDIT:

Not that it really makes much of a difference, but if you only want to reference $url once:

INSERT INTO `links` (`url`, `url_hash`, `parent_id`)
    SELECT url, MD5(url), max(id)
    FROM (SELECT id, $url as url
          FROM `links`
          WHERE `url` = $parentUrl
          LIMIT 1
         ) t;