CODEWITHSUNDEEP
sqlsql-server
Skipper
Skipper

Reputation: 23

Subqueries in SQL

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

Upvotes: 0

Views: 153

Answers (2)

russ
russ

Reputation: 624

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

Upvotes: 0

Fred Francisco
Fred Francisco

Reputation: 146

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

Upvotes: 1

Related Questions