SQL Server Delete

usually the delete statements are straighforward and similar in all sql programming.

delete from THE_TABLE_HERE where COLUMN = 'DELETE CRITERIA HERE'

Select all Open in new window

Would you mind giving out more details about what you are trying to do ?

Thank you.

Why don't you explain what you are trying to do?  I can't interpret what your query does and I don't want t speculate and start sending queries your way.

I have a sessions table.  I have a users table.  Some of the users are anonymous as indicated by their Id = Username (Their username is a Guid).  I want to delete anonymous users and their sessions.  Here, I am just trying to delete their sessions.

A simplified version:

DELETE FROM Sessions AS S
WHERE Users.Id = Users.Username

Select all Open in new window

BTW - The error I am getting is "The multi-part identifier 'DatabaseName.dbo.Users.Id' could not be bound."

Incorrect syntax near the keyword 'as'.  I have tried this before....  I am not a complete newbie at SQL Server, starting to think it has something to do with SQL Azure.

DELETE FROM [DatabaseName].[dbo].[Sessions] as S
	INNER JOIN [DatabaseName].[dbo].[Users] as U on S.UserId = U.Id
        WHERE U.Id = U.Username
GO

Select all Open in new window

What are the exact names of the columns / tables you are trying to work with and what is the value for the 'anonymous' identifier in the column ?


-- 'as' just provides an alias for the table you are working with.

Good luck.

Did you try my syntax?

Anonymous users have Users.Id = Users.Username.  When someone comes into the application, right away I create an anonymous user by adding them into the database, they simply have an Id generated (Guid) and I assign that Id as their Username as well.  They don't have any other information (until they sign in or register).  Here are the necessary fields I am working with.

Users Table:
Id
....
Username
....

Sessions Table:
UserId
....

delete from sessions s where S.userid in (select id from users u, sessions ss where ss.userid = u.id and u.username = 'Guid')

Select all Open in new window

delete from sessions s where S.userid in (select u.id from users u, sessions ss where ss.userid = u.id and u.username = 'Guid')

Oh, sorry guys I missed that.  This would work but I am getting an error because my Users.Id (guid) is different from Users.Username (varchar).  See new question here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26871234.html

DELETE S FROM [DatabaseName].[dbo].[Sessions] as S
	INNER JOIN [DatabaseName].[dbo].[Users] as U on S.UserId = U.Id
    WHERE U.Id = U.Username
GO

Select all Open in new window

Thanks guys!

There is no need to start a new question:

DELETE S FROM [DatabaseName].[dbo].[Sessions] as S
      INNER JOIN [DatabaseName].[dbo].[Users] as U on S.UserId = U.Id
    WHERE cast(U.Id as char(36)) = U.Username
GO