Weird SQL behavior with INSERT and WHERE NOT EXISTS

I have this query, which should insert an amount if an amount equals or higher for the same ID doesn't already exists in the table.

I tried this under MySQL 5.1 and MSSQL 2k5 :

MySQL

INSERT IGNORE INTO test开发者_高级运维 (id, amount) SELECT 6, 50 FROM test WHERE NOT EXISTS (SELECT 1 FROM test WHERE amount >= 50 AND id = 6) LIMIT 1

MSSQL

INSERT INTO test (id, amount) SELECT 6, 50 FROM test WHERE NOT EXISTS (SELECT TOP 1 1 FROM test WHERE amount >= 50 AND id = 6)

This query works fine IF there is already at least one entry in the table. If the table is empty, it will never work. It's the same behavior under MySQL (5.1) and MSSQL (2005). I don't understand why. Anybody has an explanation and a way to fix this query to work even if the table is completely empty?

EDIT : I need this for MySQL mostly...

UPDATE : I started a new question specifically for MySQL : MySQL Problem with inserting a row with certain conditions

It fails because the select statement selects values of 6 and 50 based on the number of rows returned. Since your table is empty - no rows are returned to have these values.

Modify it to

 INSERT INTO test (id, amount) SELECT 6, 50 WHERE NOT EXISTS (SELECT TOP 1 1 FROM test WHERE amount >= 50 AND id = 6) 

Change your query as follows:

INSERT INTO test (id, amount) 
SELECT 6, 50 FROM test 
WHERE (SELECT count(*) FROM test WHERE amount >= 50 AND id = 6) = 0 

I tried this step by step:

    -- create a test-table with one entry
SELECT id = 1, amount = 50 
into #TEST

-- see if statement works when table is not empty
INSERT INTO #test (id, amount)  
SELECT id = 6, amount = 50 
FROM #test  
WHERE NOT EXISTS (SELECT * FROM #test WHERE amount >= 50 AND id = 6) 

-- show content of test-table
select * from #test

-- empty the test table
truncate table #test

-- try to insert a first dataentry
INSERT INTO #test (id, amount)  
SELECT id = 6, amount = 50 
FROM #test  
WHERE NOT EXISTS (SELECT * FROM #test WHERE amount >= 50 AND id = 6) 
union
SELECT id = 6, amount = 50 
where (SELECT C = COUNT(*)  FROM #test WHERE amount >= 50 AND id = 6) = 0

-- show content of test-table
select * from #test

-- remove Test-table
drop table #test

First i tried to reproduce the behaviour and yes it's weird. The union - think manages the empty-table case and voilà this works fine.

ok, thats the real question. Try this, using a second table (#test2):

    -- create a test-table with one entry
SELECT id = 1, amount = 50 
into #TEST

-- create a second test-table with one entry
SELECT id = 1, amount = 50 
into #TEST2

-- see if statement works when table is not empty
INSERT INTO #test (id, amount)  
SELECT id = 6, amount = 50 
FROM #test  
WHERE NOT EXISTS (SELECT * FROM #test WHERE amount >= 50 AND id = 6) 

-- show content of test-table
select * from #test

-- empty the test table
truncate table #test

INSERT INTO #test (id, amount) SELECT 6, 50 WHERE NOT EXISTS (SELECT TOP 1 1 FROM #test WHERE amount >= 50 AND id = 6)  

-- try to insert a first dataentry
INSERT INTO #test (id, amount)  
SELECT id = 6, amount = 50 
FROM #test  
WHERE NOT EXISTS (SELECT * FROM #test WHERE amount >= 50 AND id = 6) 
union
SELECT id = 6, amount = 50 
from #test2
where (SELECT C = COUNT(*)  FROM #test) = 0


-- show content of test-table
select * from #test

-- remove Test-table
drop table #test

(Assuming you know the tables with a preceeding #, this are temporary tables in MS-SQL).

BonyT has explained the reason already.