Subquery returned more than - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Subquery returned more than

Author

Topic

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 02:15:31

i have two tables, table taget has column DimCampId i want to update this column with DimCampId from other table.
please see the query and corret me. where i am doing wrong.
AHhh.
I am forgetting.. SQL.. :(

update Targettable set DimCampId =(select DimCampId from BAACKUPServer.dbo.DimCamp where BAACKUPServer.dbo.DimCamp.CampName=Targettable.campName)

---------------
Msg 512, Level 16, State 1, Line 1

1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 02:18:48

When I used Top1 it update the column....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-17 : 03:19:20

[code]UPDATE tt
SET DimCampId = dc.DimCampId
FROM Targettable AS tt
INNER JOIN BAACKUPServer.dbo.DimCamp AS dc ON dc.CampName = tt.campName[/code]

E 12°55'05.63"
N 56°04'39.26"

pratikasthana17
Starting Member

3 Posts

Posted - 2009-03-18 : 04:30:34

Hi,

you can use

update Targettable set DimCampId in(select DimCampId from BAACKUPServer.dbo.DimCamp where BAACKUPServer.dbo.DimCamp.CampName=Targettable.campName)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 04:55:13

quote:
Originally posted by pratikasthana17

update Targettable set DimCampId in(select DimCampId from BAACKUPServer.dbo.DimCamp where BAACKUPServer.dbo.DimCamp.CampName=Targettable.campName)

Set DimCampID to what value?

E 12°55'05.63"
N 56°04'39.26"

abcefg
Starting Member

16 Posts

Posted - 2009-03-25 : 10:02:52

Is there Any way to optimize this query.
By boss said. WHy you are runing Select Statement again & again.!!!!!!

Update TAGDW.dbo.DIMdiscount
Set StartDate=(Select StartDate from discountcode where TAG.dbo.discountcode.discountid =TAGDW.dbo.DimDiscount.DimDiscountNativeId
and discountcode.updatedon >=(select Max(LastRunTime) from TAGDW.dbo.Configsettings) ),
ExpiryDate=(Select ExpiryDate from discountcode where TAG.dbo.discountcode.discountid =TAGDW.dbo.DimDiscount.DimDiscountNativeId
and discountcode.updatedon >=(select Max(LastRunTime) from TAGDW.dbo.Configsettings) ),
ValueDiscount=(Select ValueDiscount from discountcode where TAG.dbo.discountcode.discountid =TAGDW.dbo.DimDiscount.DimDiscountNativeId
and discountcode.updatedon >=(select Max(LastRunTime) from TAGDW.dbo.Configsettings) ),
PercentDiscount=(Select PercentDiscount from discountcode where TAG.dbo.discountcode.discountid =TAGDW.dbo.DimDiscount.DimDiscountNativeId
and TAG.dbo.discountcode.updatedon >=(select Max(LastRunTime) from TAGDW.dbo.Configsettings) )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 10:45:36

Your boss is right.

DECLARE	@LastRunTime DATETIME

SELECT	@LastRunTime = MAX(LastRunTime)
FROM	TAGDW.dbo.ConfigSettings

UPDATE		w
SET		w.StartDate = dc.StartDate,
		w.ExpiryDate = dc.ExpiryDate,
		w.ValueDiscount = dc.ValueDiscount,
		w.PercentDiscount = dc.PercentDiscount
FROM		TAGDW.dbo.DimDiscount AS w
INNER JOIN	TAG.dbo.DiscountCode AS dc ON dc.DiscountID = w.DimDiscountNativeID
WHERE		dc.UpdatedOn >= @LastRunTime

E 12°55'05.63"
N 56°04'39.26"

abcefg
Starting Member

16 Posts

Posted - 2009-03-25 : 10:50:26

Update TAGDW.dbo.DimDiscountBand
Set
TAGDW.dbo.DimDiscountBand.LowerLimit =A.LowerLimit ,
TAGDW.dbo.DimDiscountBand.UpperLimit= A.UpperLimit ,
TAGDW.dbo.DimDiscountBand.BandName=A.BandName ,
TAGDW.dbo.DimDiscountBand.PercentDiscount =A.PercentDiscount ,
TAGDW.dbo.DimDiscountBand.ValueDiscount=A.ValueDiscount
from TAGDW.dbo.DimDiscountBand B Right join STG_DimDiscountBand A
on b.fkDimDiscountId =a.fkDimDiscountId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources