SQL Server Delete

I am using SQL Azure.  I am trying to test something and I can't get this delete query to work.  I am guessing it has something to do with the fact that my criteria for deleting a record has nothing to do with that table I am deleting from.

DELETE FROM [DatabaseName].[dbo].[Sessions]
    WHERE [DatabaseName].[dbo].[Users].[Id] = [DatabaseName].[dbo].[Users].[Ud]
GO

Open in new window

MidwestAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
again (the syntax is that you have to delete from the ALIAS)



delete s
from sessions as S
inner join users as U
on U.id = s.UserID
0
 
CarlsbergFTWCommented:
usually the delete statements are straighforward and similar in all sql programming.

delete from THE_TABLE_HERE where COLUMN = 'DELETE CRITERIA HERE'

Open in new window


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

Thank you.
0
 
BrandonGalderisiCommented:
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MidwestAuthor Commented:
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

Open in new window

0
 
MidwestAuthor Commented:
BTW - The error I am getting is "The multi-part identifier 'DatabaseName.dbo.Users.Id' could not be bound."
0
 
BrandonGalderisiCommented:
delete s
from sessions s
  inner join users u
    on s.User_Id  = u.Id -- Is this the right join between sessions and users?
    and u.Id = u.Username
0
 
Éric MoreauSenior .Net ConsultantCommented:
delete s
from sessions as S
inner join users as U
on U.id = s.UserID
0
 
CarlsbergFTWCommented:
delete from sessions where sessions.id in (select id from users, sessions where sessions.id = users.id and users.username = 'anonymous')

Open in new window



try tuning that to fit your needs and be careful when working with delete statements. As Brandon tried to explain to you, the more information the better for a potential valid answer.
0
 
MidwestAuthor Commented:
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

Open in new window

0
 
CarlsbergFTWCommented:
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.
0
 
BrandonGalderisiCommented:
Did you try my syntax?
0
 
MidwestAuthor Commented:
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
....
0
 
Éric MoreauSenior .Net ConsultantCommented:
delete s
from sessions as S
inner join users as U
on U.id = s.UserID
and u.id = u.username
0
 
CarlsbergFTWCommented:


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

Open in new window

0
 
CarlsbergFTWCommented:
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')
0
 
MidwestAuthor Commented:
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

Open in new window

0
 
MidwestAuthor Commented:
Thanks guys!
0
 
BrandonGalderisiCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.