Subqueries in SQL

Question

I'm trying to figure out a way to do a update on the database but I keep failing. I can't really think of something else.

  IF @EventID = 9
   BEGIN
   declare @HwanLevel int;
   UPDATE SRO_VT_SHARD.._Char set HwanLevel = 1 WHERE CharID in (SELECT TOP(1) EXP,LEVEL,CONTRIBUTION FROM SRO_VT_SHARD.._CharTrijob WHERE JobType = 1 AND CharID = @CharID)
   UPDATE SRO_VT_SHARD.._Char set HwanLevel = 2 WHERE CharID in (SELECT TOP(1) EXP,LEVEL,CONTRIBUTION FROM SRO_VT_SHARD.._CharTrijob WHERE JobType = 2 AND CharID = @CharID)
   UPDATE SRO_VT_SHARD.._Char set HwanLevel = 3 WHERE CharID in (SELECT TOP(1) EXP,LEVEL,CONTRIBUTION FROM SRO_VT_SHARD.._CharTrijob WHERE JobType = 3 AND CharID = @CharID)
   END

OHH and when I tried to use it I kept getting; "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I have tried joins etc and case when but didn't really work out. what I'm trying to do is:

SELECT Top(1) EXP,Contribution,Level
THEN check if Jobtype = 1 then set HwanLevel to 1
if jobtype = 2 then set hwanlevel to 2
etc until 3

marc_s · Accepted Answer · 2014-07-05 07:36:31Z

1

The subquery you're returning has too many fields declared. Return only one field. So it should look:

SELECT CharID 
FROM SRO_VT_SHARD.._CharTrijob 
WHERE JobType = 1 AND CharID = @CharID

edited Jul 5, 2014 at 7:36

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 5, 2014 at 7:11

Fred Francisco

14610 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Skipper Over a year ago

Uhm.. I wanna get the top charids using exp,contribution,level columns from SRO_VT_SHARD.._CharTrijob for this to work. Do you know any other way to do it?

Fred Francisco Over a year ago

UPDATE _Char set HwanLevel = (SELECT TOP(1) JobType FROM _CharTrijob WHERE _CharTrijob.CharID = _Char.CharID)

Skipper Over a year ago

I meant.. I gotta select TOP(1) Exp, Contribution, Level Not JobType.. that's what I have been failing in until now.

Fred Francisco Over a year ago

Why do you need to return "Exp, Contribution, Level" fields if you only need the _Char.JobType to be updated from _CharTrijob.JobType field? You have no use for these 3 fields? According to you, you need _Char.JobType to be updated with the same value as the Top 1 from _CharTrijob.JobType with the same charid. Am I correct?

Skipper Over a year ago

I'm giving a title in my server, The top player in job gets a title. There are 3 titles; #1 Hunter #1 Trader #1 Thief So I gotta use Select TOP(1) EXP,Contribution,Level for it to work. To get the top players in the server. Without it the whole query is useless.. Jobtype is only used to check if the player is thief or hunter or trader

russ · Accepted Answer · 2014-07-06 13:07:53Z

0

Your top(1) is only going to respond to an ORDER BY clause in your query. It would be better to do somehting along the lines of:

UPDATE SRO_VT_SHARD.._Char SET HwanLevel = Jobtype WHERE Jobtype = (select top(1)
Jobtype FROM SRO_VT_SHARD.._CharTrijob ORDER BY EXP,LEVEL,CONTRIBUTION)

Of course it depends on the sequence of columns in the ORDER BY, if the given sequence produces the top player then good, otherwise you can re-order the sequence within the ORDER BY ie:

...ORDER BY CONTRIBUTION,EXP,LEVEL

How about using the variable, setting the variable according to the Jobtype given by the TOP 1 SELECT and then updating the other table from the variable:

DECLARE @HWANLEVEL INT
SET @HWANLEVEL = (SELECT TOP 1 Jobtype FROM SRO_VT_SHARD.._CharTrijob 
                  ORDER BY EXP,LEVEL,CONTRIBUTION)
UPDATE SRO_VT_SHARD.._Char SET HwanLevel = @HWANLEVEL

edited Jul 6, 2014 at 13:07

answered Jul 5, 2014 at 20:38

russ

6244 silver badges7 bronze badges

1 Comment

russ Over a year ago

I was mainly just trying to make it clear that it would need an ORDER BY clause in it...I've added a bit more to my answer which might make it make work.

Collectives™ on Stack Overflow

Subqueries in SQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related