add new constraint to existing query

Posted on 2008-11-05
Last Modified: 2010-05-18
In SQL 2000 I need to add an additional constraint to a query I use that finds unused numeric PK's in a varchar field.  

The current query (below) comes from angelll's solution here:

The constraint I need to add is: return only those values that don't already exist in et_objects.object_id (another table/column).  Unfortunately, in that table the values have an 's' prepended in front of them.  

For example, if the first unused integer in customers.customer_code is 123 (returned by the current query) but et_objects.object_id contains 's123' and 's124' then I need to return 125 instead.

Any help greatly appreciated.

Here is the current query that returns the smallest unused integer in customers.customer_code:

declare @t table ( v int )
insert into @t (v) select CAST( customer_code AS INT)
  FROM customers
 WHERE customer_code NOT LIKE '%[^0-9]%'
select min(t.v) + 1
  from @t t
  left join @t n
    on n.v = t.v + 1
 where n.v is null

Question by:ottenm
    LVL 5

    Expert Comment

    on your column you can do

    select replace( yourCol, 's', '' ) from yourTable

    replace( n, 's', '' )


    select n = 's134'

    ) src

    Author Comment

    Thank you nicolasdiogo.  I can do 'replace', or just concatenate.  What I need help with is getting the query to avoid values that conflict with the other table.

    Accepted Solution

    I think I got it!  Id' been trying to add it to the insert, but came up with the following by adding it to the select.  Just need to test it out....

    declare @t table ( v int )
    insert into @t (v) select CAST( customer_code AS INT)
      FROM customers
     WHERE customer_code NOT LIKE '%[^0-9]%'

    select min(t.v) + 1
      from @t t
      left join @t n
        on n.v = t.v + 1
      left join et_objects o
        on o.object_id = 's' + cast(t.v + 1 as varchar)
     where n.v is null and o.object_id is null

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now