Delete Join statement 2

[44nato44]

Hi

I got a table called tblAccessRights where I want to delete data and then I have a table called tblTriSecActis where I have the users who I want to keep in the table tblAccessRights. the values tblAccessRights.Userid and tblTriSecActis.UserName are the values to be compared.

I made this statement, but that one does not work :

strSQL = "DELETE tblAccessRights.*"
strSQL = strSQL & " from tblAccessRights inner join tblTriSecActis "
strSQL = strSQL & " ON tblAccessRights.Userid <> tblTriSecActis.UserName"
strSQL = strSQL & " WHERE tblAccessRights.ToolTask = TRUE"


I hope somebody can spot where it goes wrong ?

 

[gmmastros]

What database are you using? Microsoft Access, SQL Server, MySQL, Oracle, etc... The correct advice may depend on it.

Also, why do you have a <> here? Usually, if those are the 2 columns you are comparing, then an = is the correct thing to use.

strSQL = "DELETE tblAccessRights.*"
strSQL = strSQL & " from tblAccessRights inner join tblTriSecActis "
strSQL = strSQL & " ON tblAccessRights.Userid [!]<>[/!] tblTriSecActis.UserName"
strSQL = strSQL & " WHERE tblAccessRights.ToolTask = TRUE"

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[44nato44]

the database is a ms access database and I used <> because I wanted to have the values which did not match.

Is that possible ?

 

[gmmastros]

There is a useful "trick" that I use when creating delete statements (especially when multiple tables are involved).

I usually write the query as a select statement. Only after I get the correct rows returned by the select statement, I change it to a delete. Ex:

[tt]
Select *
From Table1
Inner Join Table2
On Table1.Col1 = Table2.Col1
Where Table1.SomeColumn = 'Blah'
[/tt]

Once I get the query to return the correct rows, it is a simple modification to turn it in to a delete query.

[!]Delete Table1[/!]
[s]Select *[/s]
From   Table1
       Inner Join Table2
         On Table1.Col1 = Table2.Col1
Where  Table1.SomeColumn = 'Blah'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[44nato44]

cool thanks, but what if I want to delete the differences between the two tables, how will I go about that ?

 

[gmmastros]

By the way, I suspect <> is not quite what you want. From your description, I'm not sure what you want, but I still think <> is not it.

You see, you used inner join for the tables, which leads me to think you want the tables to join somehow. What most people don't realize is that you can usually change the behavior of a join based on other conditions within the query. You can get an inner join to behave like a cross join:

Select *
From   Table1
       Inner Join Table2
         On 1=1

You can get a left join to behave like an inner join.

Select *
From   Table1
       Left Join Table2
         On Table1.PKColumn = Table2.PKColumn
Where  Table2.PKColumn Is Not NULL

There are plenty of other ways to change the join behavior. Your query would act *almost* like a cross join by matching all unrelated rows.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Delete the differences? Do you mean.... you only want to keep rows that exist in both tables?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I read your question again, and I *think* I understand what you are trying to accomplish.

If a userid exists in the tblAccessRights table, but does not exist in teh tblTriSecActis table, you want to delete it from tblAccessRights. Is this correct?

If so, you can get a list of the users like this:

Select tblAccessRights.*
From   tblAccessRights
       Left Join tblTriSecActis
         on tblAccessRights.UserId = tblTriSecActis.UserName
Where  tblTriSecActis.UserName Is NULL

And delete like this:

Delete tblAccessRights
From   tblAccessRights
       Left Join tblTriSecActis
         on tblAccessRights.UserId = tblTriSecActis.UserName
Where  tblTriSecActis.UserName Is NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[44nato44]

awesome .. thanks gmmastros

I think this is the second you show me the light.. appreciate your help

 

[wvdba]

gmmastros,
is this correct:

Delete tblAccessRights
From   tblAccessRights
       Left Join tblTriSecActis
         on tblAccessRights.UserId = tblTriSecActis.UserName
Where  tblTriSecActis.UserName Is NULL

or shold lit be:

Delete *
From   tblAccessRights
       Left Join tblTriSecActis
         on tblAccessRights.UserId = tblTriSecActis.UserName
Where  tblTriSecActis.UserName Is NULL

just wondering.
thanks.

 

[gmmastros]

wvdba,

I suppose it depends on the type of database. I am MUCH more familiar with Microsoft SQL Server than I am with any other type of database.

For Microsoft SQL Server, you first example (which matches the code I posted) is correct. Think about it this way, the * usually represents "all columns". This is certainly true in a select query. i.e. Select * means, select all columns. When you are deleting data, it is NOT possible to delete from individual columns. Deleting removes rows from the table. If you wanted to delete the data from certain columns within a row, you would use an update query and set the values for certain columns to null, 0, or empty string ('').

Also (in Microsoft SQL Server), you MUST specify the table name immediately following the DELETE keyword when you have multiple tables involved. This allows SQL Server to know (for sure) which table you are actually deleting from. With SQL Server, it is not possible to delete from multiple tables with a single query. You can have triggers and foreign keys with cascade delete that makes it appear as though you are deleting from multiple tables with a single delete statement, but that is just appearances.

Remember, I have a sql server background. Each database system may be different.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

gmmastros,
thanks for the clarification. my background is in oracle, db2 and access. from looking at the sql statement, it kind of scared me - as if "you're deleting a table!!!". which i know can't be true, since you have to use DTOP table to accomplish that. but, this command (delete) is kind of strange in sql server.
thanks again.

 

[44nato44]

Below is the code I used, I guess tblAccessRights.* will delete the row where it matches

strSQL = "Delete tblAccessRights.* "
strSQL = strSQL & " From [tblAccessRights] "
strSQL = strSQL & " Left Join [tblTriSecActis]"
strSQL = strSQL & " on tblAccessRights.UserId = tblTriSecActis.UserName"
strSQL = strSQL & " Where tblTriSecActis.UserName Is NULL