This T-SQL query completed with errors: Invalid Column name, please help

I've the following SQL statement to pick a random value which doesn't exist in a DB table:

select CONVERT(INT, 1 + 999999999 * rand()) as NEW_DVAL
WHERE NEW_DVAL NOT IN (SELECT FIELD_DVAL FRO开发者_StackOverflow社区M dbo.MASTER_REF)

It completed with errors: Invalid column name.

I find no way to get it done, please kindly help.

Thanks! William

if you want a guarantee return value, unless the table includes all possible values, you win need to keep generating new randoms on collision, something like that:

DECLARE @NEW_DVAL AS INT;

WHILE 1=1
BEGIN
   SET @NEW_DVAL = CONVERT(INT, 1 + 999999999 * RAND());

   IF NOT EXISTS(SELECT * FROM dbo.MASTER_REF WHERE @NEW_DVAL = FIELD_DVAL)
      BREAK;
END;

SELECT @NEW_DVAL;

Unfortunately, you can't use an alias ("as NEW_DVAL") in a where clause. You can do this:

 select NEW_DVAL from (
   select CONVERT(INT, 1 + 999999999 * rand()) as NEW_DVAL
 ) a
 WHERE NEW_DVAL NOT IN (SELECT FIELD_DVAL FROM dbo.MASTER_REF)

However, that I suspect that your query won't do what you want anyway. The "select convert(...)" bit will give you ONE row, which the "where" bit will either filter out, or not.

If the randomly selected new dval already exists, the query will return no rows - it won't automatically loop until it finds an unused one.

Take a look at K Ivanov's answer for a solution to this.