May 27, 2009 at 2:41 pm
Ok, Im very new at this... I will try to explain what I am trying to do and hopefully someone will be able to help me.
I have this code
UPDATE dbo.'TABLE1'
SET 'colum1' = 1, 'colum2' = 0,
WHERE ('column1' = 0) AND ('column2' >= 10);
the condition here is:
it sets column1 to 1 and column2 to 0 if column1 is equal to 0 and column2 is grater or equal to 10.
what I need to do is add to the condition... I want to delete the contents of table2 and table3 if that condition is met. I hope i explained this well. 😎
I dont know hows its done but I'm guessing it's something like this.
UPDATE dbo.'TABLE1'
SET 'colum1' = 1, 'colum2' = 0,
delete table2
delete table3
WHERE ('column1' = 0) AND ('column2' >= 10);
but that gives me an error >.<
May 27, 2009 at 2:57 pm
Have a look at the EXISTS () clause in Books Online. You can test if at least 1 row matches the criteria and if so do the UPDATE along with the DELETEs. Otherwise do only the UPDATE.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2009 at 3:43 pm
Frank Kalis (5/27/2009)
Have a look at the EXISTS () clause in Books Online. You can test if at least 1 row matches the criteria and if so do the UPDATE along with the DELETEs. Otherwise do only the UPDATE.
none of the columns on table 2 and 3 are the same as the ones in table1
May 27, 2009 at 3:43 pm
In case Frank's answer isn't completely clear you can't do it in one statement, you, but you can do it in one script, you need to use the IF construct - something like this.
IF EXISTS (Select * FROM Table1 WHERE col1 = 0 and col2 >= 10)
BEGIN
UPDATE ...
DELETE FROM table2 WHERE ...
DELETE FROM table3 WHERE ...
END
May 27, 2009 at 3:53 pm
Tom Brown (5/27/2009)
In case Frank's answer isn't completely clear you can't do it in one statement, you, but you can do it in one script, you need to use the IF construct - something like this.
IF EXISTS (Select * FROM Table1 WHERE col1 = 0 and col2 >= 10)
BEGIN
UPDATE ...
DELETE FROM table2 WHERE ...
DELETE FROM table3 WHERE ...
END
I think i uderstand what you are trying to say here, but can i just say DELETE FROM table2. and leave it as that? I need all contents from table2 to be gone once that takes place.
EDIT:
Let me be more specific
I have 3 Tables, all with different columns in them, one called user_character, user_skill, user_slot.
This is what I have.
UPDATE dbo.user_character
SET wLevel = 1, dwExp = 0, wStatPoint = 250 * (Reborn + 1), nHP=106, nMP=16, dwMoney = dwMoney - (50000000), wStr = 6, wDex = 3, wCon = 4, wSpr = 2, wPosX = 336, wPosY = 366, Reborn = Reborn + 1, wMapIndex = 7, wSkillPoint = 0
WHERE (byPCClass = 0) AND (wlevel >= 170) AND (dwMoney >= (500)) AND (Reborn < 50);
This codes, changes things around on user_character table... I need something that will look for the information byPCClass, wlevel, dwMoney and Reborn (which are in the user_character table) and not only change the info on user_character but also clear the tables user_skill ans user_slot for those who meet the criteria on byPCClass, wlevel, dwMoney and Reborn... hope Im being clear.
May 27, 2009 at 4:21 pm
So if an update needs to take place you want to delete all records from table2?
or only specific records from table2. If its the latter, then how do you identify which records to delete from table2?
May 27, 2009 at 4:27 pm
Tom Brown (5/27/2009)
So if an update needs to take place you want to delete all records from table2?or only specific records from table2. If its the latter, then how do you identify which records to delete from table2?
things to be deleted from user_character are already identified in the coding, for user_skill and user_slot I need to delete all contents of those tables. All I need is for it to delete them according to the criteria on user_character to be met.
May 27, 2009 at 4:33 pm
Then this should work
IF EXISTS (SELECT * FROM dbo.user_character WHERE (byPCClass = 0) AND (wlevel >= 170) AND (dwMoney >= (500)) AND (Reborn = 170) AND (dwMoney >= (500)) AND (Reborn < 50);
DELETE FROM dbo.user_skill;
DELETE FROM dbo.user_slot;
END
May 28, 2009 at 12:10 am
Sorry, if I was a bit lazy in my reply 🙂
I still keep forgetting about it, but another alternative in SQL Server 2005 might look something like this:
DECLARE @Output TABLE (table definition according to whatever the Primary key in dbo.user_character looks like);
UPDATE dbo.user_character
SET
wLevel = 1,
dwExp = 0,
wStatPoint = 250 * (Reborn + 1),
nHP=106,
nMP=16,
dwMoney = dwMoney - (50000000),
wStr = 6,
wDex = 3,
wCon = 4,
wSpr = 2,
wPosX = 336,
wPosY = 366,
Reborn = Reborn + 1,
wMapIndex = 7,
wSkillPoint = 0
OUTPUT
INSERTED.
INTO
@Output
WHERE
(byPCClass = 0 AND
wlevel >= 170 AND
dwMoney >= 500) AND
(Reborn < 50);
IF EXISTS (SELECT 1
FROM @output)
DELETE FROM dbo.user_skill;
DELETE FROM dbo.user_slot;
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply