SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Fixing a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bissa
Starting Member

27 Posts

Posted - 01/21/2013 :  02:19:55  Show Profile  Reply with Quote
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
52309 Posts

Posted - 01/21/2013 :  03:25:12  Show Profile  Reply with Quote

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/

Go to Top of Page

bissa
Starting Member

27 Posts

Posted - 01/21/2013 :  03:36:15  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/21/2013 :  03:43:55  Show Profile  Reply with Quote
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

27 Posts

Posted - 01/21/2013 :  04:06:57  Show Profile  Reply with Quote
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

27 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/22/2013 :  10:54:23  Show Profile  Reply with Quote
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

27 Posts

Posted - 01/22/2013 :  11:37:14  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/23/2013 :  01:09:23  Show Profile  Reply with Quote
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

27 Posts

Posted - 01/23/2013 :  06:54:19  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/23/2013 :  10:46:01  Show Profile  Reply with Quote
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

27 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/23/2013 :  13:58:55  Show Profile  Reply with Quote
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

27 Posts

Posted - 01/23/2013 :  18:48:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000