SQL IF statement won't execute

Question

I don't know SQL, and I'm having trouble finding an example for what I need to do.

I have a table which I want to delete from, in the case that an entry's isGuest column is 1. I then delete from other tables which referenced that entry.

As a C++ developer, I basically want to do an if check to see if a given account (selected with an accountID) has an isGuest of 0, and return from the function if so (skipping the deletes).

If I try this, I get an

invalid column name 'isGuest'

error. I know that the table and column names are correct, so I'm at a loss. I'm getting worried that SQL can't actually do what I want to do here based on some confusing forum posts I've seen already.

Can you post your SQL query? Also, what database are you using? — Mike Christensen, Oct 29 '12 at 16:44
what I tried: SELECT isGuest from dbo.Account WHERE AccountID=@AccountId IF( isGuest = 0 ) BEGIN return 0; END I THINK I'm using mysql. I'm not sure how to verify. — Tim Richard, Oct 29 '12 at 16:46
table definitions and sql in your question would be a good start. Also be mindful that SQL works on sets of data, so you won't be executing an 'if' statement, you'll just be deleting data (or not, whichever) that is in a given set as identified by your guestId = 0. — Dave Richardson, Oct 29 '12 at 16:47
I think my problem here is that I delete from multiple tables based on the contents of one of them. I can't just go to each table and "delete where ID is X and isGuest is 1". Is there some way to exit a stored procedure early in the event that the first table delete doesn't happen? — Tim Richard, Oct 29 '12 at 16:52
@TimRichard - A suggestion for using this site. For SQL questions always include DDL for your tables, some dummy data, and the query. Use http://sqlfiddle.com/ if at all possible! — Robert Kaucher, Oct 29 '12 at 17:23
I've been attempting the two answers given, and they seem close, but not quite. It looks like I'm not using mysql. I'm writing a stored procedure in Microsoft SQL Server Management Studio Express 2005, version 9.00.2047.00 Does this help at all? The last thing I tried was the answer involving declaring the @Guest bit, but it complains about incorrect syntax near '=' in the when/then line — Tim Richard, Oct 29 '12 at 21:03

score 0 · Answer 1 · edited May 23 '17 at 11:48

If you are using MySql, I believe you are able to DELETE from multiple tables in a single statement, something like:

DELETE T1, T2, T3
FROM table1 T1
    LEFT JOIN table2 T2 ON T1.Id = T2.Id -- Or whatever links your main table to others
    LEFT JOIN table3 T3 ON T1.Id = T3.Id
WHERE T1.isGuest = 0 -- Filter the records you want to delete.  This is what you were trying the accomplish with IF(isGuest = 0)

Otherwise, you will need to output the Ids of the records you want to delete into a temp table and use that to delete from your other tables. This question gives a good explanation.

SchmitzIT · Answer 2 · 2012-10-29T18:14:02.143

-1

DECLARE @guest bit;

SELECT 
  CASE isGuest
    WHEN 0 THEN @guest = 0
    ELSE @guest =1
  END As GuestYesNo
FROM 
   dbo.Account
WHERE
  AccountID = @AccountID;

IF @Guest = 0
BEGIN
  -- DoStuff;
END 
ELSE
  -- DoOtherStuff;

CASE statement IF..ELSE statement

edited Oct 29 '12 at 18:14

answered Oct 29 '12 at 17:20

SchmitzIT

9,227
9
65
92

Updated with an `IF` statement following the cASE, and added references to both. – SchmitzIT Oct 29 '12 at 18:14

SQL IF statement won't execute

2 Answers2