Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Delete

Posted on 2011-03-08
18
Medium Priority
?
410 Views
Last Modified: 2014-11-12
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

0
Comment
Question by:Midwest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35069569
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35069578
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
 

Author Comment

by:Midwest
ID: 35069650
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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 

Author Comment

by:Midwest
ID: 35069656
BTW - The error I am getting is "The multi-part identifier 'DatabaseName.dbo.Users.Id' could not be bound."
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 35069671
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
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1200 total points
ID: 35069672
delete s
from sessions as S
inner join users as U
on U.id = s.UserID
0
 
LVL 3

Assisted Solution

by:CarlsbergFTW
CarlsbergFTW earned 400 total points
ID: 35069708
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
 

Author Comment

by:Midwest
ID: 35069741
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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35069766
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1200 total points
ID: 35069819
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35069827
Did you try my syntax?
0
 

Author Comment

by:Midwest
ID: 35069857
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
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1200 total points
ID: 35069889
delete s
from sessions as S
inner join users as U
on U.id = s.UserID
and u.id = u.username
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35069892


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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35069906
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
 

Author Comment

by:Midwest
ID: 35069923
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
 

Author Closing Comment

by:Midwest
ID: 35069959
Thanks guys!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35069961
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Or at least that’s the word according to a new blog from Tech Target on AWS’s new Managed Services (MS) offering. According to the blog, AWS is launching their AWS MS program to expedite the adoption of cloud by Fortune 1000 and Global 2000 companie…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question