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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Fixing a query

Author  Topic 

bissa
Starting Member

32 Posts

Posted - 2013-01-21 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-21 : 03:25:12
[code]
UPDATE c
SET c.Str = m.CharName ,
c.StatPoint = m.FromCharName
FROM _Char c
INNER JOIN _Message m
ON m.CharName = c.CharName
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-21 : 03:36:15
Thank you but I want the query to continue execute Set Strength = 1, Intellect = 1, StatPoint = 1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-21 : 03:43:55
what do you mean continue execute? Illustrate with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-21 : 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

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-22 : 09:13:59
what I need can be done or it can't? :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-22 : 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/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-22 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 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/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-23 : 06:54:19
Perfect! it worked like it should

What about if I want to DELETE instead of SET?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 10:46:01
use DELETE instead of UPDATE

why not make a try yourselves modifying given suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-23 : 11:17:37
I tried, it gives error "Incorrect Syntax near 'DELETE' Expecting SET.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 13:58:55
look for syntax of DELETE and make a try. Will help if you face any issues

http://msdn.microsoft.com/en-us/library/ms189835.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bissa
Starting Member

32 Posts

Posted - 2013-01-23 : 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
Go to Top of Page
   

- Advertisement -