Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay|||Absolutely right My mistake
I have updated my Query and if i run the below Query it Gives ame an Error as
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 JAX_EOL_COntract
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
please advice
Thanks
"Aaron [SQL Server MVP]" wrote:
> What is the purpose of Contract_ID in the inner SELECT?
> Look at the query you're running. You're saying, basically, "... SET
> some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
> the SET statement supposed to do with it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> >
> > Update COntract
> > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > FROM LinkedServer.Closed_Contract
> > where Disp_Date is not Null)
> >
> > This Query gives me an Error as
> > Only one expression can be specified in the select list when the subquery
> is
> > not introduced with EXISTS.
> >
> > My Select Query does return more then 1 record. is their any way to
> perform
> > this Query in a better way as the select Query retrieves more then 200,000
> > records.
> >
> > LinkedServer = Linked server from one Server to another
> >
> > Thanks
> > samay
>
>|||Well if there are two dates in Closed_Contract where disp_date is not null,
let's say 20041110 and 20041108, which one do you want? You can use SELECT
MIN() or SELECT MAX() or some other condition which enforces only one row to
be returned.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
> Absolutely right My mistake
> I have updated my Query and if i run the below Query it Gives ame an Error
as
> 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 JAX_EOL_COntract
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> please advice
> Thanks
> "Aaron [SQL Server MVP]" wrote:
> > What is the purpose of Contract_ID in the inner SELECT?
> >
> > Look at the query you're running. You're saying, basically, "... SET
> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
is
> > the SET statement supposed to do with it?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> > >
> > > Update COntract
> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > > FROM LinkedServer.Closed_Contract
> > > where Disp_Date is not Null)
> > >
> > > This Query gives me an Error as
> > > Only one expression can be specified in the select list when the
subquery
> > is
> > > not introduced with EXISTS.
> > >
> > > My Select Query does return more then 1 record. is their any way to
> > perform
> > > this Query in a better way as the select Query retrieves more then
200,000
> > > records.
> > >
> > > LinkedServer = Linked server from one Server to another
> > >
> > > Thanks
> > > samay
> >
> >
> >|||Following up on Aaron's comment, possibly you need to add a restriction to
the subquery so it only returns the record for the current contract.
Something like
Update JAX_EOL_COntract JC
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract CC
where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Mike A.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:echAR10xEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Well if there are two dates in Closed_Contract where disp_date is not
> null,
> let's say 20041110 and 20041108, which one do you want? You can use
> SELECT
> MIN() or SELECT MAX() or some other condition which enforces only one row
> to
> be returned.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message
> news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
>> Absolutely right My mistake
>> I have updated my Query and if i run the below Query it Gives ame an
>> Error
> as
>> 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 JAX_EOL_COntract
>> Set dDispositionDate = (SELECT Disp_Date
>> FROM LinkedServer.Closed_Contract
>> where Disp_Date is not Null)
>> please advice
>> Thanks
>> "Aaron [SQL Server MVP]" wrote:
>> > What is the purpose of Contract_ID in the inner SELECT?
>> >
>> > Look at the query you're running. You're saying, basically, "... SET
>> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
> is
>> > the SET statement supposed to do with it?
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>> >
>> >
>> > "KritiVerma@.hotmail.com"
> <KritiVermahotmailcom@.discussions.microsoft.com>
>> > wrote in message
> news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
>> > >
>> > > Update COntract
>> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
>> > > FROM LinkedServer.Closed_Contract
>> > > where Disp_Date is not Null)
>> > >
>> > > This Query gives me an Error as
>> > > Only one expression can be specified in the select list when the
> subquery
>> > is
>> > > not introduced with EXISTS.
>> > >
>> > > My Select Query does return more then 1 record. is their any way to
>> > perform
>> > > this Query in a better way as the select Query retrieves more then
> 200,000
>> > > records.
>> > >
>> > > LinkedServer = Linked server from one Server to another
>> > >
>> > > Thanks
>> > > samay
>> >
>> >
>> >
>|||> Following up on Aaron's comment, possibly you need to add a restriction to
> the subquery so it only returns the record for the current contract.
> Something like
> Update JAX_EOL_COntract JC
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract CC
> where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Yes, this will work, assuming a one-to-one relationship (which, based on
table names, seems likely).