Post reply

Subquery returned more than 1 value.

  • March 22, 2011 at 9:40 am

    #247305

    I am trying to update table A with table b earlylate as below and i get an error as

    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.

    update tableA

    SET TableA.validateTime = (select tableB.Earlylate from tableB

    where tableA.ID = tableB.ID)

    Where Exists

    ( select tableB.Earlylate from tableB

    where tableA.ID = tableB.ID)

    table A

    [ID] [int] NOT NULL,

    [Type] [nvarchar](5) NULL,

    [Plans] [nvarchar](4000) NULL,

    [Notification] [nvarchar](4000) NULL,

    [Verification] [nvarchar](4000) NULL,

    [ValidateTime] [nvarchar](4000) NULL,

    table B

    [ID] [int] NOT NULL,

    [Type] [nvarchar](5) NOT NULL,

    OpID] [smallint] NOT NULL,

    [Descrip] [nvarchar](4000) NULL,

    [Desc] [nvarchar](100) NULL,

    [In] [nvarchar](4000) NULL,

    [Earlydate [datetime] NULL,

    [Latedate [datetime] NULL,

    [EarliestAndLatest] [nvarchar](4000) NULL

  • March 22, 2011 at 9:45 am

    #1301947

    your ttableB.Earlylate has more than one value...you might want the MAX date or the MIN date, depending on your logic, instead

    update tableA

    SET TableA.validateTime = (select MAX(tableB.Earlylate) from tableB

    where tableA.ID = tableB.ID)

    Where Exists

    ( select tableB.Earlylate from tableB

    where tableA.ID = tableB.ID)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 22, 2011 at 10:15 am

    #1301972

    Thanks that solved the problem

Viewing 3 posts - 1 through 2 (of 2 total)

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