Post reply

Do not allow insert if record exists with certain condition

  • August 24, 2006 at 9:44 pm

    #94356

    What is the best way to do this?

    Query 1 would not insert since AcctNo "aaa"

    exists with a Code "x"

    Query 2 would insert since AcctNo "bbb"

    exists, but not with a Code "x"

    MyTable:

    AcctNo Code

    aaa x

    aaa z

    bbb z

    Query 1:

    INSERT INTO MyTable

    (AcctNo, Code)

    SELECT

    'aaa', 'y'

    WHERE ??? MyTable.Code does not have a record = 'x'

    Query 2:

    INSERT INTO MyTable

    (AcctNo, Code)

    SELECT

    'bbb', 'y'

    WHERE ??? MyTable.Code does not have a record = 'x'

     

  • August 24, 2006 at 11:06 pm

    #656767

    This works, probably not best way to do this?  Any suggestions would be appreciated.

    INSERT INTO InsertIfNoMatch

    (AcctNo, Code)

    SELECT

    AcctNo, Code

    FROM InsertIfNoMatch_DATA

    WHERE AcctNo <>

    (SELECT AcctNo FROM InsertIfNoMatch

     WHERE Code = 'z'

     AND AcctNo = InsertIfNoMatch.AcctNo)

     

  • August 24, 2006 at 11:36 pm

    #656768

    Well it worked until I got more than a record or so in it.  Knew it looked ugly, lol.

    error:

    Server: Msg 512, Level 16, State 1, Line 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

  • August 25, 2006 at 12:39 am

    #656770

    IF NOT EXISTS (SELECT * FROM MyTable WHERE AcctNo = 'aaa' and code = 'x')

     INSERT INTO MyTable

     (AcctNo, Code)

     SELECT

     'aaa', 'y'

    IF NOT EXISTS (SELECT * FROM MyTable WHERE AcctNo = 'bbb' and code = 'x')

     INSERT INTO MyTable

     (AcctNo, Code)

     SELECT

     'bbb', 'y'

    N 56°04'39.16"
    E 12°55'05.25"

  • August 28, 2006 at 10:01 am

    #657133

    Set a unique index on those columns, and SQL Server will not allow duplicate values. If this column is the primary key, define it as such and SQL Server will enforce uniqueness. Your other choice is to create a stored procedure that checks to see if it exists, and inserts or updates depending on what is found. You could also do this with a Check constraint, but the stored proc method is the best by far.

  • August 28, 2006 at 10:47 am

    #657151

    declare @AcctNo varchar(10),@Code varchar(1)

    set @AcctNo='aaa'

    set @Code='y'

    if not exists (select AcctNo,Code from myTable where AcctNo=@AcctNo and Code<>'x')

     insert into MyTable (AcctNo,Code) values (@AcctNo,@Code)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply