?
Solved

Incorrect syntax near the keywork 'join'

Posted on 2005-04-18
15
Medium Priority
?
564 Views
Last Modified: 2012-08-13
Hi,

I'm getting the error: "Incorrect syntax near the keywork 'join'" when I try running the following query:

delete from save_the_universe outer join dupdata on
dupdata.univ_rundt = save_the_universe.univ_rundt
and dupdata.cusip = save_the_universe.cusip
and dupdata.gvkey = save_the_universe.gvkey
and dupdata.FR1000 = save_the_universe.FR1000
and dupdata.FR1000G = save_the_universe.Fr1000G
and dupdata.FR1000V = save_the_universe.FR1000V
and dupdata.FR2000 = save_the_universe.FR2000
and dupdata.Fr2000G = save_the_universe.FR2000G
and dupdata.FR2000V = save_the_universe.FR2000V
and dupdata.FR2500 = save_the_universe.FR2500
and dupdata.FR2500G = save_the_universe.FR2500G
and dupdata.FR2500V = save_the_universe.FR2500V
and dupdata.FR3000 = save_the_universe.FR3000
and dupdata.FR3000V = save_the_universe.FR3000V
and dupdata.FR3000G = save_the_universe.FR3000G

I'm trying to get rid of duplicate rows in table save_the_universe. I copied to dupdata the rows that are duplicates in save_the_universe with the following query:

select [univ_rundt], [cusip], [gvkey], [FR1000], [FR1000G], [FR1000V], [FR2000], [FR2000G], [FR2000V], [FR2500], [FR2500G], [FR2500V], [FR3000], [FR3000G], [FR3000V]
into ##dupdata
from universe
group by [univ_rundt], [cusip], [gvkey], [FR1000], [FR1000G], [FR1000V], [FR2000],
[FR2000G], [FR2000V], [FR2500], [FR2500G], [FR2500V], [FR3000], [FR3000G], [FR3000V]
having count(*) > 1

and now I want to delete them from save_the_universe.

Do you know why my delete query is returning that error?

Thank you,

Rebecca
0
Comment
Question by:rss2
  • 5
  • 5
  • 3
  • +1
15 Comments
 

Author Comment

by:rss2
ID: 13805111
Actually, this is quite urgent. Thanks!
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 13805137
Hi the problem might be that you use outer join

but I think you have to specify either

left outer join

right outer join

full outer join

depending on what you want... in your case I think full outer join

Scott
0
 

Author Comment

by:rss2
ID: 13805195
Hi,

I tried to execute:

delete from save_the_universe full outer join dupdata on
dupdata.univ_rundt = save_the_universe.univ_rundt
and dupdata.cusip = save_the_universe.cusip
and dupdata.gvkey = save_the_universe.gvkey
and dupdata.FR1000 = save_the_universe.FR1000
and dupdata.FR1000G = save_the_universe.Fr1000G
and dupdata.FR1000V = save_the_universe.FR1000V
and dupdata.FR2000 = save_the_universe.FR2000
and dupdata.Fr2000G = save_the_universe.FR2000G
and dupdata.FR2000V = save_the_universe.FR2000V
and dupdata.FR2500 = save_the_universe.FR2500
and dupdata.FR2500G = save_the_universe.FR2500G
and dupdata.FR2500V = save_the_universe.FR2500V
and dupdata.FR3000 = save_the_universe.FR3000
and dupdata.FR3000V = save_the_universe.FR3000V
and dupdata.FR3000G = save_the_universe.FR3000G

.. but got "Incorrect syntax near the keyword 'full'." Any other ideas?

Is it not possible to do a delete with a join?

Thanks
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 15

Expert Comment

by:Colosseo
ID: 13805208
now that you mention it i am not sure... i just tried and i get the same error.

I will keep looking

Scott
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 13805239
It seems possible just the syntax is different

have a look here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_9lut.asp

Example D

Scott
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 2000 total points
ID: 13805251
so you would have

delete save_the_universe from save_the_universe full outer join dupdata on

Scott
0
 

Author Comment

by:rss2
ID: 13805309
Excellent. That worked. THANK YOU!
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 13805325
Your welcome and thanks for the grade

Cheers

Scott
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13805326
Did you use it with a FULL OUTER JOIN ??

I tend to think it would delete all the records

I'd use an INNER JOIN instead
0
 

Author Comment

by:rss2
ID: 13805525
Yes, it did delete all the records. :)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13805584
Sorry I shouted when I saw the last post but obviously you didn't hear me ...
And I didn't type fast enough ...

The full outer join basically means that the join will work whether there's a matching record in dupdata or not.

Do you have a backup of the table ??
0
 

Author Comment

by:rss2
ID: 13805823
Yes, I've learned THAT lesson before. :) It wasn't a problem. Thanks for checking. :)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13805850
Then just changing the "full outer join " into an "inner join " in colosseo's answer
should do the trick.
Have a nice day

Hilaire
0
 
LVL 10

Expert Comment

by:PSSUser
ID: 13805899
Try using an exists query, instead of a join:

delete from save_the_universe
Where exists (select dupdata.* from dupdata  
Where dupdata.univ_rundt = save_the_universe.univ_rundt
and dupdata.cusip = save_the_universe.cusip
and dupdata.gvkey = save_the_universe.gvkey
and dupdata.FR1000 = save_the_universe.FR1000
and dupdata.FR1000G = save_the_universe.Fr1000G
and dupdata.FR1000V = save_the_universe.FR1000V
and dupdata.FR2000 = save_the_universe.FR2000
and dupdata.Fr2000G = save_the_universe.FR2000G
and dupdata.FR2000V = save_the_universe.FR2000V
and dupdata.FR2500 = save_the_universe.FR2500
and dupdata.FR2500G = save_the_universe.FR2500G
and dupdata.FR2500V = save_the_universe.FR2500V
and dupdata.FR3000 = save_the_universe.FR3000
and dupdata.FR3000V = save_the_universe.FR3000V
and dupdata.FR3000G = save_the_universe.FR3000G)
0
 
LVL 10

Expert Comment

by:PSSUser
ID: 13805905
oops, sorry got interrupted before I posted. Should have refreshed!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 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