asked on

SQL Query error

Attempting to delete rows with duplicate fields using this query:

Delete FROM TEMP_PROP t1 CROSS JOIN TEMP_PROP t2
where t1.RowID <> t2.RowID
and t1.situs_dpbc = t2.situs_dpbc

I received this error:
Line 1: Incorrect syntax near 't1'.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

I presume you want this:

Delete FROM TEMP_PROP t1 
WHERE EXISTS ( SELECT NULL FROM TEMP_PROP t2 
           where t1.RowID <> t2.RowID
             and t1.situs_dpbc = t2.situs_dpbc
              )

Open in new window

marknicks

ASKER

Thanks for a quick response. Unfortunately we haven't found it yet.

The first answer gave this error:
The table 'temp_prop' is ambiguous.

The second gave this error:
Line 1: Incorrect syntax near 't1'.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marknicks

ASKER

still getting the syntax error near t1

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marknicks

ASKER

well it at least ran without errors. but i'm still testing.
I think it's pretty close to the right solution.

marknicks

ASKER

This series of queries turned out to be the best approach, elminating the FoxPro routines.

"select * into #Temp_Dups from Temp_Prop
      where (rowid in (select MIN(rowid) as min_id from temp_prop
            group by mailing_zip, mailing_addr_line1
       having count(*) > 1)) AND (Session_ID = '" & strSession_ID & "')"

"Delete temp_prop from TEMP_PROP t1
      WHERE (EXISTS ( SELECT NULL FROM TEMP_PROP t2 where(t1.rowid <> t2.rowid)
and t1.mailing_zip + t1.mailing_addr_line1 = t2.mailing_zip + t2.mailing_addr_line1))
AND (Session_ID = '" & strSession_ID & "')"

"alter table #Temp_Dups drop column rowid"

"insert Temp_Prop select * from #Temp_Dups"

I appreciate all your comments as they pointed me in the right direction!