locked
subquery returned more than 1 value RRS feed

  • Question

  • I have an insert trigger that copies records to a history table when fired, based on certain criteria.   If a column is equal to a value, then I do some processing and ultimately insert the record into a history table.

     

    If I do the below:

    ----------------

    IF (select FIELDA from inserted) = 'ATEST'

    ----------------

    this is ok unless there are multiple records, then I get

    Msg 512, Level 16, State 1, Procedure ABCDEF, Line 12
    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.

    -----------

    I can change the

    IF (select FIELDA from inserted) = 'ATEST'

    to include distinct

    ---------------

    IF (select distinct FIELDA from inserted) = 'ATEST'

    ---------------

    but, i don't know that the column will always be a distinct value, in which i think I would get

    the same error as above.    I guess my question is, how do you get around this in a trigger?  Use a cursor?

     

     

      

    Friday, November 26, 2010 3:09 PM

Answers

  • More, you can use a query like this in your trigger :

    try to use :

    insert HistoryTable (fileds1,....)

    select fileds1,.... from inserted where FIELDA ='ATEST'


    Best regards


    The important thing here which you are missing in your code sqlguy10 is the where clause which allows for multiple values.

    In the Select part you could apply your processing if it is simple SELECT column1 X 4, etc

    This method will ensure you are processing all the changes. If you change your query to use Top 1 you will only be catchin gteh first change.

    Seth

     

     


    http://lqqsql.wordpress.com
    • Marked as answer by sqlguy10 Monday, November 29, 2010 12:23 AM
    Friday, November 26, 2010 4:43 PM

All replies

  • try to use :

    if exists (select 1 from inserted where FIELDA ='ATEST')

     

     


    Best regards
    Friday, November 26, 2010 3:17 PM
  • More, you can use a query like this in your trigger :

    try to use :

    insert HistoryTable (fileds1,....)

    select fileds1,.... from inserted where FIELDA ='ATEST'


    Best regards
    • Proposed as answer by Seth Lynch Friday, November 26, 2010 4:43 PM
    Friday, November 26, 2010 3:23 PM
  • use top 1
    Friday, November 26, 2010 3:51 PM
  • More, you can use a query like this in your trigger :

    try to use :

    insert HistoryTable (fileds1,....)

    select fileds1,.... from inserted where FIELDA ='ATEST'


    Best regards


    The important thing here which you are missing in your code sqlguy10 is the where clause which allows for multiple values.

    In the Select part you could apply your processing if it is simple SELECT column1 X 4, etc

    This method will ensure you are processing all the changes. If you change your query to use Top 1 you will only be catchin gteh first change.

    Seth

     

     


    http://lqqsql.wordpress.com
    • Marked as answer by sqlguy10 Monday, November 29, 2010 12:23 AM
    Friday, November 26, 2010 4:43 PM
  • Thanks

    Monday, November 29, 2010 12:23 AM