On Wed, 16 Sep 2015 08:23:04 +1000
Barry Smith <smith.barryp at gmail.com> wrote:
> As for your original query: think about just the select clause (you
> can run it independently). This will return ('magnetohydrodynamics',
> 1) for each row in the table.
It took me a bit to understand what you meant. I also think there's a
better answer than resorting to LIMIT 1.
To clarify, the OP's query is (reformatted):
> INSERT INTO TAGS ( NAME, COUNT )
> SELECT 'magnetohydrodynamics', 1
> FROM TAGS -- <- the error
> WHERE NOT EXISTS (
> SELECT * FROM TAGS
> WHERE NAME = 'magnetohydrodynamics'
> );
The existence test is against the whole TAGS table. As long as the
name 'magnetohydrodynamics' appears in the table, SELECT will return
zero rows. But -- your point -- if the name tested does *not* appear in
the table, SELECT will return as many rows as are in the table.
The solution is simply to say what's meant instead:
INSERT INTO TAGS ( NAME, COUNT )
SELECT 'magnetohydrodynamics',1
WHERE NOT EXISTS (
SELECT 1 FROM TAGS
WHERE NAME = 'magnetohydrodynamics'
);
As to the OP's question about where he went wrong, the query as
presented should not have created the results he showed. Those results
could be explained, though, if "magnetohydrodynamics" was misspelled in
the WHERE clause. I suspect that's what went wrong.
--jkl
