Professional Web Applications Themes

Update more than one record (Subquery returned more than 1 value) - Microsoft SQL / MS SQL Server

Hi, I have a problem with an update statement that I can't run the way a want to. I want to run it this way: UPDATE tblElectionForm SET Ele_Sta_ID = 6 WHERE Ele_Sta_ID = 5 But when I run this statement I receive the following error: "Subquery returned more than 1 value..." I have solved this with a cursor: DECLARE InternalID INT DECLARE Claes_Cursor CURSOR FOR SELECT Ele_InternalID FROM tblElectionForm WHERE Ele_Sta_ID = 5 OPEN Claes_Cursor FETCH NEXT FROM Claes_Cursor INTO InternalID WHILE (FETCH_STATUS = 0) BEGIN UPDATE tblElectionForm SET Ele_Sta_ID = 6 WHERE Ele_InternalID = InternalID FETCH NEXT FROM ...

  1. #1

    Default Update more than one record (Subquery returned more than 1 value)

    Hi,

    I have a problem with an update statement that I can't run the way a want
    to. I want to run it this way:

    UPDATE tblElectionForm SET Ele_Sta_ID = 6
    WHERE Ele_Sta_ID = 5

    But when I run this statement I receive the following error:

    "Subquery returned more than 1 value..."

    I have solved this with a cursor:

    DECLARE InternalID INT
    DECLARE Claes_Cursor CURSOR FOR

    SELECT Ele_InternalID FROM tblElectionForm WHERE Ele_Sta_ID = 5

    OPEN Claes_Cursor

    FETCH NEXT FROM Claes_Cursor INTO InternalID

    WHILE (FETCH_STATUS = 0)
    BEGIN
    UPDATE tblElectionForm
    SET Ele_Sta_ID = 6
    WHERE Ele_InternalID = InternalID

    FETCH NEXT FROM Claes_Cursor INTO InternalID
    END

    CLOSE Claes_Cursor
    DEALLOCATE Claes_Cursor

    But looping through all the records takes a long time, to long time.

    Does anybody know how to write the UPDATE statement?

    I would be very thankful if I anyone could help me with this.

    Here are the SQL-scripts for the tables (I have removed the uninteresting
    parts):

    CREATE TABLE [dbo].[tblStatus] (
    [Sta_ID] [int] NOT NULL ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblElectionForm] (
    [Ele_InternalID] [int] IDENTITY (1, 1) NOT NULL ,
    [Ele_Sta_ID] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblStatus] WITH NOCHECK ADD
    CONSTRAINT [PK_tblStatus] PRIMARY KEY CLUSTERED
    (
    [Sta_ID]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblElectionForm] WITH NOCHECK ADD
    CONSTRAINT [PK_tblElectionForm] PRIMARY KEY CLUSTERED
    (
    [Ele_InternalID]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblElectionForm] ADD
    CONSTRAINT [FK_tblElectionForm_tblStatus] FOREIGN KEY
    (
    [Ele_Sta_ID]
    ) REFERENCES [dbo].[tblStatus] (
    [Sta_ID]
    ) ON DELETE CASCADE NOT FOR TION
    GO



    BR

    Mikael Jirhage


    Mikael Guest

  2. #2

    Default Update more than one record (Subquery returned more than 1 value)

    I dont see any problem with your query...make sure you
    have primary key constraint on table tblElectionForm.
    which version of sql server you are running
     
    run the way a want 
    error: 
    Ele_Sta_ID = 5 
    long time. 
    this. 
    the uninteresting 
    naveen Guest

  3. #3

    Default Re: Update more than one record (Subquery returned more than 1 value)

    DId you noticed locking on the tables, that might be the
    thing. Try excuting sp_who or sp_lock to indentify the problem.
    If someone is locking the resultset in a query or something you might wait,
    till the lockis released.

    Jens Süßmeyer



    Jens Guest

  4. #4

    Default Re: Update more than one record (Subquery returned more than 1 value)

    Hi,

    Thanks for your answer.

    I now know what the problem is. I have a trigger that I forgot to mention,
    sorry about that. Here is the trigger:

    CREATE TRIGGER [update_tblElectionForm] ON dbo.tblElectionForm
    FOR UPDATE AS

    DECLARE nInternalID int
    DECLARE nAuditID int
    DECLARE strTable varchar(50)
    SET strTable = 'tblElectionForm'
    SET nInternalID = (SELECT Ele_InternalID FROM Inserted)
    --Status
    IF UPDATE (Ele_Sta_ID)
    BEGIN
    DECLARE strStatusOld char (1)
    DECLARE strStatusNew char (1)
    SET strStatusOld = (SELECT Ele_Sta_ID FROM Deleted)
    SET strStatusNew = (SELECT Ele_Sta_ID FROM Inserted)
    SET nAuditID = (SELECT Aus_ID FROM tblAuditTrailSelection WHERE Aus_Table
    = strTable AND Aus_Column = 'Ele_Sta_ID')
    IF strStatusOld <>strStatusNew
    INSERT INTO tblAuditTrail (Aud_InternalID, Aud_Aus_ID, Aud_Action,
    Aud_ValueBefore, Aud_ValueAfter, Aud_User)
    VALUES (nInternalID, nAuditID, 'UPDATE', strStatusOld, strStatusNew,
    SYSTEM_USER)
    END

    How do I fix the trigger to handle multi-row update? I guess the problem is
    when I do "SET nInternalID = (SELECT Ele_InternalID FROM Inserted)".

    Regards

    Mikael Jirhage


    "naveen" <com> wrote in message
    news:0da901c36659$0c94d9b0$gbl... 
    > run the way a want 
    > error: 
    > Ele_Sta_ID = 5 
    > long time. 
    > this. 
    > the uninteresting [/ref]


    Mikael Guest

  5. #5

    Default Re: Update more than one record (Subquery returned more than 1 value)

    Hi,

    Thanks for your answer.

    I now know what the problem is. I have a trigger that I forgot to mention,
    sorry about that. Here is the trigger:

    CREATE TRIGGER [update_tblElectionForm] ON dbo.tblElectionForm
    FOR UPDATE AS

    DECLARE nInternalID int
    DECLARE nAuditID int
    DECLARE strTable varchar(50)
    SET strTable = 'tblElectionForm'
    SET nInternalID = (SELECT Ele_InternalID FROM Inserted)
    --Status
    IF UPDATE (Ele_Sta_ID)
    BEGIN
    DECLARE strStatusOld char (1)
    DECLARE strStatusNew char (1)
    SET strStatusOld = (SELECT Ele_Sta_ID FROM Deleted)
    SET strStatusNew = (SELECT Ele_Sta_ID FROM Inserted)
    SET nAuditID = (SELECT Aus_ID FROM tblAuditTrailSelection WHERE Aus_Table
    = strTable AND Aus_Column = 'Ele_Sta_ID')
    IF strStatusOld <>strStatusNew
    INSERT INTO tblAuditTrail (Aud_InternalID, Aud_Aus_ID, Aud_Action,
    Aud_ValueBefore, Aud_ValueAfter, Aud_User)
    VALUES (nInternalID, nAuditID, 'UPDATE', strStatusOld, strStatusNew,
    SYSTEM_USER)
    END

    How do I fix the trigger to handle multi-row update? I guess the problem is
    when I do "SET nInternalID = (SELECT Ele_InternalID FROM Inserted)".

    Regards

    Mikael Jirhage

    "Jens Süßmeyer" <jsuessmeyer[RemoveME]web.de> wrote in message
    news:phx.gbl... 
    wait, 


    Mikael Guest

  6. #6

    Default Re: Update more than one record (Subquery returned more than 1 value)

    Hello Mikael !

    Multi-Row Update can be handled within a cursor, where you Select the
    INSERTED
    Or DELETED table INTO the resultset. (Whatever you need). By fetching them
    one by one
    you can make your changes.

    Jens Süßmeyer


    "Mikael Jirhage" <se> schrieb im Newsbeitrag
    news:#phx.gbl... 
    Aus_Table 
    strStatusNew, 
    is 
    > wait, 
    >
    >[/ref]


    Jens Guest

  7. #7

    Default Re: Update more than one record (Subquery returned more than 1 value)

    > How do I fix the trigger to handle multi-row update?

    Untested version below. The only issue with this technique is when a
    primary key is updated. This is not a problem here since you are using
    an identity column as the PK.

    CREATE TRIGGER [update_tblElectionForm]
    ON dbo.tblElectionForm
    FOR UPDATE AS

    IF UPDATE (Ele_Sta_ID)
    BEGIN
    INSERT INTO tblAuditTrail
    (
    Aud_InternalID,
    Aud_Aus_ID,
    Aud_Action,
    Aud_ValueBefore,
    Aud_ValueAfter,
    Aud_User
    )
    SELECT
    i.Ele_InternalID,
    (
    SELECT Aus_ID
    FROM tblAuditTrailSelection
    WHERE Aus_Table = 'tblElectionForm' AND
    Aus_Column = 'Ele_Sta_ID'
    ),
    'UPDATE',
    d.Ele_Sta_ID,
    i.Ele_Sta_ID,
    SYSTEM_USER
    FROM inserted i
    JOIN deleted d ON
    d.Ele_InternalID = i.Ele_InternalID
    WHERE
    d.Ele_Sta_ID <> i.Ele_Sta_ID
    END

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Mikael Jirhage" <se> wrote in message
    news:%phx.gbl... 
    Here is 
    Aus_Table 
    strStatusNew, 
    problem is [/ref]
    cannot [/ref]
    updating a [/ref][/ref]
    a [/ref][/ref]
    time. 
    > > uninteresting 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

  8. #8

    Default Re: Update more than one record (Subquery returned more than 1 value)

    It works great. Thanks a lot. Before this change an update took about 105
    seconds (14300 records) and now it takes only 2 seconds.

    Once again, thanks.

    Mikael Jirhage

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    I.ele_sta_id, 


    Mikael Guest

  9. #9

    Default Re: Update more than one record (Subquery returned more than 1 value)

    > With his solution it only takes 

    David's LEFT JOIN method is more efficient here. If you are certain
    that tblAuditTrailSelection will always contain the requested row,
    consider an INNER JOIN. This will improve performance further.

    The bottom line is that set-based processing is usually *much* more
    efficient than cursors.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP


    "Mikael Jirhage" <se> wrote in message
    news:%23B3$phx.gbl... 
    105 
    received 
    takes 
    > >
    > > Untested version below. The only issue with this technique is when[/ref][/ref]
    a [/ref]
    using [/ref][/ref]
    anyway. 
    > > Aus_Table [/ref][/ref]
    Aud_Action, 
    > > strStatusNew, 
    > > problem is [/ref][/ref]
    Inserted)". [/ref][/ref]
    that 
    > > updating a [/ref][/ref]
    way [/ref][/ref]

    > > time. 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

Similar Threads

  1. Replies: 1
    Last Post: October 3rd, 04:20 PM
  2. Returned Page Won't Update When Using Custom Template & Textbox
    By Greg Pyatt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 18th, 07:38 PM
  3. Replies: 0
    Last Post: July 28th, 05:04 PM
  4. Replies: 0
    Last Post: July 16th, 07:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139