Results 1 to 5 of 5

Thread: UPDATE failing

  1. #1
    Join Date
    Feb 2002
    Location
    Riga, Latvia
    Posts
    7

    Unanswered: UPDATE failing

    Hallo folk!

    I wanted to update a tinyint column of a table that contains nothing but
    NULL values. I issued this statement:

    UPDATE Subscriber
    SET Sub_Address_Status = 0
    WHERE Sub_Address_Status IS NULL

    In response i got:

    Server: Msg 512, Level 16, State 1, Line 0
    Subquery returned more than 1 value. This is illegal when the subquery
    follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
    Command has been aborted.

    Nothing was updated... I can sucessfully update a single record in that
    table.

    What could be wrong?

    Thanks a lot!

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    This message cannot come from this statement on it's own (unless you have managed to hit a bug in sql server somehow - this isn't involved in replication is it?).
    Do you have a trigger on the table?
    Otherwise are you executing something else at the same time?
    Last edited by nigelrivett; 02-17-02 at 22:29.

  3. #3
    Join Date
    Feb 2002
    Location
    Riga, Latvia
    Posts
    7
    Yes, there is an update trigger that contains:

    UPDATE Subscriber
    SET Sub_Address_Status = 0
    WHERE
    Sub_Address_Status = 2 AND
    Subscriber_ID = (SELECT Subscriber_ID FROM Inserted)

    It's been made by our developers, but looks like the last line should look something like this:
    Subscriber_ID IN (SELECT Subscriber_ID FROM Inserted)

    Looks like this is the culprit?

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You should explain to the developers what a trigger is.

    I would do it like this - should be faster for a large inserted table but probably won't make much difference for small ones.

    UPDATE Subscriber
    SET Sub_Address_Status = 0
    from inserted
    WHERE
    Sub_Address_Status = 2 AND
    Subscriber_ID = inserted.Subscriber_ID

  5. #5
    Join Date
    Feb 2002
    Location
    Riga, Latvia
    Posts
    7
    OK, thanks i'll try that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •