| Author |
 Topic  |
|
|
bissa
Starting Member
27 Posts |
 Posted - 01/21/2013 : 02:19:55
|
Hello, I need help fixing a query
I have 2 tables, each table has ID and Name. I want to execute the query only the if the ID and Name matches in the 2 tables.
I'm using this query but it is not working like it should, as the query will continue to execute even if the 2 tables doesn't match.
More Info:
Table 1 has the good info each ID has a charname, there is no duplicated entries, Table 2 can have duplicated Charname with the Same ID. The Query depend on table 2, so I only want the query to get executed if the Charname and ID in Table 2 matches the Charname and ID in table 1
Declare @CharName3 Varchar(30)=(Select FromCharName From _Message Where CharName = (select CharName from _Char where CharID = @CharID))
Declare @CharName4 Varchar(30)=(Select CharName From _Char Where CharID = @CharID)
If @CharName3 = @CharName4
Begin
Update _Char Set Str = , Int = , StatPoint = where CharID = @CharID
End
Thank you in advance |
Edited by - bissa on 01/21/2013 02:25:00
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/21/2013 : 03:25:12
|
UPDATE c
SET c.Str = m.CharName ,
c.StatPoint = m.FromCharName
FROM _Char c
INNER JOIN _Message m
ON m.CharName = c.CharName
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bissa
Starting Member
27 Posts |
Posted - 01/21/2013 : 03:36:15
|
| Thank you but I want the query to continue execute Set Strength = 1, Intellect = 1, StatPoint = 1 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/21/2013 : 03:43:55
|
what do you mean continue execute? Illustrate with some sample data
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bissa
Starting Member
27 Posts |
Posted - 01/21/2013 : 04:06:57
|
Ok,
Table _Message
ID ToCharID FromCharName Message Time
3 149587 Char1 Reset 2013-01-17 00:00:00
4 149587 Char2 Reset 2013-01-17 14:49:00
You can see in this table FromCharName (Char1 and Char2) got the same ToCharID (it means Char1 and Char2 send a message, Char1 sent a message to himself and Char2 sent a message to Char1)
Second Table _Char
CharID CharName
149587 Char1
158888 Char2
You can see in this table CharName (Char1 and Char2) got different ID
I'm using a message system tool which you can use to reset your stats points, this tool only check the table _Message, when the tool see the CharName with a message "Reset" the query get executed. Now there is persons can abuse it by for example Char2 will send the message "Reset" to Char1, and once the tool will see CharName and the message "Reset" the query will get executed but the tool is meant to Char1 can reset his stats only and Char2 can reset his stats only.
ID / ToCharID / FromCharID / Message / Time
CharID / CharName
|
|
|
|
bissa
Starting Member
27 Posts |
Posted - 01/22/2013 : 09:13:59
|
| what I need can be done or it can't? :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/22/2013 : 10:54:23
|
so is the question to identify those who try to reset others?
ie
SELECT *
FROM [_Message] m
INNER JOIN [_Char] c
ON c.CharName = m.FromCharName
WHERE c.CharID <> m.ToCharID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bissa
Starting Member
27 Posts |
Posted - 01/22/2013 : 11:37:14
|
| Yea exactly, this query is fine but I want to make this query as condition, if it matches, the query "update _char set x y x" execute |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/23/2013 : 01:09:23
|
then use join in update
UPDATE c
SET c.Str = m.CharName ,
c.StatPoint = m.FromCharName
FROM _Char c
INNER JOIN _Message m
ON m.FromCharName = c.CharName
WHERE c.CharID <> m.ToCharID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bissa
Starting Member
27 Posts |
Posted - 01/23/2013 : 06:54:19
|
Perfect! it worked like it should
What about if I want to DELETE instead of SET? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/23/2013 : 10:46:01
|
use DELETE instead of UPDATE
why not make a try yourselves modifying given suggestion?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bissa
Starting Member
27 Posts |
Posted - 01/23/2013 : 11:17:37
|
| I tried, it gives error "Incorrect Syntax near 'DELETE' Expecting SET. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
|
|
bissa
Starting Member
27 Posts |
Posted - 01/23/2013 : 18:48:00
|
I tried this query but it is giving me error at the INNER
Delete t
From _Time t
WHERE Category = '0'
INNER JOIN _Message m
ON m.CharID = t.CharID
|
Edited by - bissa on 01/23/2013 18:51:31 |
|
|
| |
Topic |
|
Fixing a query
