Avatar of Midwest
Midwest
Flag for United States of America asked on

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

Microsoft SQL Server 2008Microsoft SQL Server 2005Azure

Avatar of undefined
Last Comment
BrandonGalderisi

8/22/2022 - Mon
CarlsbergFTW

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.
BrandonGalderisi

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.
Midwest

ASKER
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Midwest

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
CarlsbergFTW

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Midwest

ASKER
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

CarlsbergFTW

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
BrandonGalderisi

Did you try my syntax?
Midwest

ASKER
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
....
SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CarlsbergFTW



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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
CarlsbergFTW

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')
Midwest

ASKER
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: https://www.experts-exchange.com/questions/26871234/SQL-Server-convert-Guid-to-String.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

Midwest

ASKER
Thanks guys!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
BrandonGalderisi

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