Issue with properly declaring operators Insert query SQL Server-CodePudding

The query executes but it affects only 1 row which is the first found. I did manage to get it to work as intended here is the working query:

select * 
from Game.dbo.TCharacterAbility;

select t.user_id, i.* 
from TCharacterAbility as i 
join TCharacter as t on i.char_id = t.id
where (i.strength > '746' or 
       i.dexterity > '746' or 
       i.quickness > '746' or 
       i.mentality > '746' or 
       i.health > '746' or 
       i.intelligence > '746')

Here is the one I struggle with:

DEClARE @user_id int;
DEClARE @char_id int;

SELECT @char_id = id 
FROM TCharacter as x 
INNER JOIN tcharacterability y ON x.id = y.char_id 
WHERE (y.strength > '746' OR y.dexterity > '746' OR 
       y.quickness > '746' OR  y.mentality > '746' OR 
       y.health > '746' OR y.intelligence > '746')

SELECT @user_id = user_id 
FROM TCharacter as x 
INNER JOIN tcharacterability y ON x.id = y.char_id 
WHERE (y.strength > '746' OR y.dexterity > '746' OR 
       y.quickness > '746' OR y.mentality > '746' OR 
       y.health > '746' OR y.intelligence > '746')

INSERT INTO [User].[dbo].[TDisconnect] (user_id, server_id, char_id) VALUES (@user_id, 1, @char_id)

I've tried to declare the operators the best I could, but failed miserably.

More information about the tables

t.characterability - columns:

char_id | strength | dexterity | mentality | quickness | health | intelligence |

t.character:

id (equal to char_id from characterability) | user_id | name | etc |

There's no other shared information except for id = char_id.

My goal is this query to find all the results not just the first one and insert the proper volume of matches.

CodePudding user response：

varoables a scalar values and can not hold more than one so you need another approach for that

INSERT INTO [User].[dbo].[TDisconnect] (user_id,server_id,char_id) 
SELECT user_id,1,id 
from TCharacter as x INNER JOIN tcharacterability y on x.id =y.char_id 
where (y.strength >'746' or y.dexterity>'746' or y.quickness>'746' or y.mentality>'746' or y.health>'746' or y.intelligence>'746')