sql rank <<<not updatable because the modification affects multiple base tables>>

You can't delete from a CTE table.....you can though put all this data into a #customerDups temp table and use it to cleanup the parent table(s) by some correspondent ID/Key and using the:

...FROM #customerDups where customerRank > 1;

I dont know your data as well as you but you are going to have to try to do this without a join.

Why cant you do this?

;with customerDups as (   
SELECT customer.CUSTOMERID as CUSTOMERID
,ROW_NUMBER() OVER(PARTITION BY customer.CUSTOMERID ORDER BY customer.CUSTOMERID) as customerRank
from LM.Customer customer 
WHERE ISACTIVE='T'
)
DELETE FROM customerDups where customerRank > 1;
--I have CUSTOMER and CUSTOMER_ATTRIBUTE tables joined on CUSTOMERID column

--I am trying to remove duplicates from my sql by getting a rank and then doing
DELETE FROM customerDups where customerRank > 1;

Select all Open in new window

hi Leo,
I need the join with the LM.CUSTOMER_ATTRIBUTE table.
thx

They your going to have to do something like this

;with customerDups as (   
SELECT customer.CUSTOMERID as CUSTOMERID
,customer.NAME as NAME
,customer.STARTDATE as STARTDATE,
customer.LASTUPDATED as LASTUPDATED
,customer.MODIFIEDBY as MODIFIEDBY,
CASE 
	WHEN customerAttribute.ISMIGRATED is null and customerAttribute.TYPE = 'EDIT' THEN 'IN-PROGRESS'
ELSE
''
end as STATUS,
CASE 
	WHEN customer.LOCKEDBYUSER is NULL THEN ''
	WHEN customer.LOCKEDBYUSER is not NULL THEN customer.LOCKEDBYUSER
end as LASTUSER,
ROW_NUMBER() OVER(PARTITION BY customer.CUSTOMERID ORDER BY customer.CUSTOMERID) as customerRank
from LM.Customer customer 
	LEFT OUTER JOIN LM.CUSTOMER_ATTRIBUTE customerAttribute
		ON customer.CUSTOMERID = customerAttribute.CUSTOMERID
WHERE ISACTIVE='T'
)
DELETE FROM customerDups where customerRank > 1;
--I have CUSTOMER and CUSTOMER_ATTRIBUTE tables joined on CUSTOMERID column

--I am trying to remove duplicates from my sql by getting a rank and then doing
DELETE FROM LM.Customer 
	Join customerDups
		on customerDups.SomeKEY =  LM.Customer.SomeKey
where customerDups.customerRank > 1;

Select all Open in new window

are you saying that i need to run 2 delete statements?

>>DELETE FROM customerDups where customerRank > 1;

>>DELETE FROM LM.Customer
      Join customerDups
            on customerDups.SomeKEY =  LM.Customer.SomeKey
where customerDups.customerRank > 1;

My mistake no

;with customerDups as (   
SELECT customer.CUSTOMERID as CUSTOMERID
,customer.NAME as NAME
,customer.STARTDATE as STARTDATE,
customer.LASTUPDATED as LASTUPDATED
,customer.MODIFIEDBY as MODIFIEDBY,
CASE 
	WHEN customerAttribute.ISMIGRATED is null and customerAttribute.TYPE = 'EDIT' THEN 'IN-PROGRESS'
ELSE
''
end as STATUS,
CASE 
	WHEN customer.LOCKEDBYUSER is NULL THEN ''
	WHEN customer.LOCKEDBYUSER is not NULL THEN customer.LOCKEDBYUSER
end as LASTUSER,
ROW_NUMBER() OVER(PARTITION BY customer.CUSTOMERID ORDER BY customer.CUSTOMERID) as customerRank
from LM.Customer customer 
	LEFT OUTER JOIN LM.CUSTOMER_ATTRIBUTE customerAttribute
		ON customer.CUSTOMERID = customerAttribute.CUSTOMERID
WHERE ISACTIVE='T'
)

--I am trying to remove duplicates from my sql by getting a rank and then doing
DELETE FROM LM.Customer customer 
	Join customerDups cd
		on cd.SomeKEY =  customer.SomeKey
where customerRank > 1;

Select all Open in new window

Since this is a delete please to a
Select * into TableBackup
from DeleteTable.

Just to be safe.

DELETE FROM LM.Customer customer  -- i get an error on this line
      INNER JOIN customerDups cd
            on cd.SomeKEY =  customer.SomeKey
where customerRank > 1;

error is
>>>Incorrect syntax near 'customer'.

i think it should be >>DELETE customer FROM LM.Customer customer
right ?

Yes remove the alias you cant have alias in a delete
try

DELETE FROM LM.Customer  
	Join customerDups
		on customerDups.SomeKEY =  Customer.SomeKey
where customerRank > 1;

Select all Open in new window

the problem is
DELETE FROM LM.Customer customer  -- gives an error..Incorrect syntax near 'customer'.
but DELETE customer FROM LM.Customer customer (what i thought) is also incorrect since
i dont want to delete from Customer  table but i want to delete from customerDups resultset.

when i do
DELETE customerDups  FROM LM.Customer customer  i get same error as the first time
>>View or function 'mandateDups' is not updatable because the modification affects multiple base tables.

Well the Dup table is derived from Customer table so yes you are deleting from Customer table. The CTE helps you Identify the Dup Rows. In the CTE you have to bring the Primary Key of Customer table. Then use that same key that qualifies in your condition to delete.

Your logic should resemble this:

;with CTE MyDups as (
Select Pkey, Customer
,ROW_NUMBER() Over (Partition by Customer Order by SomeValue Desc) rn
From MyCustomerTable
)

Delete from MyCustomerTable
	Join MyDups 
		on MyDups.PKey = MyCustomerTable.PKey
Where MyDups.rn > 1

Select all Open in new window

sorry what you mean by CTE ? i am not aware of that.

also, you said
>>>> the Dup table is derived from Customer table so yes you are deleting from Customer table
But I dont want to delete anything from Customer table since that is the master table.
I want to delete only from customerDups .

CTE = Common Table Expression

This is your CTE

;with customerDups as (   
SELECT customer.CUSTOMERID as CUSTOMERID
,customer.NAME as NAME
,customer.STARTDATE as STARTDATE,
customer.LASTUPDATED as LASTUPDATED
,customer.MODIFIEDBY as MODIFIEDBY,
CASE 
	WHEN customerAttribute.ISMIGRATED is null and customerAttribute.TYPE = 'EDIT' THEN 'IN-PROGRESS'
ELSE
''
end as STATUS,
CASE 
	WHEN customer.LOCKEDBYUSER is NULL THEN ''
	WHEN customer.LOCKEDBYUSER is not NULL THEN customer.LOCKEDBYUSER
end as LASTUSER,
ROW_NUMBER() OVER(PARTITION BY customer.CUSTOMERID ORDER BY customer.CUSTOMERID) as customerRank
from LM.Customer customer 
	LEFT OUTER JOIN LM.CUSTOMER_ATTRIBUTE customerAttribute
		ON customer.CUSTOMERID = customerAttribute.CUSTOMERID
WHERE ISACTIVE='T'
)

Select all Open in new window

You cant delete from your CTE.

1. Do you have Dups in your Customer table?

2. Your CTE may have customers several times because your joining to an attributes table. A customer usually has many attributes so that join will have them multiple times.  

Think we first step back and see what your issue really is.

ok thanks

>> Do you have Dups in your Customer table?
No.

>>Your CTE may have customers server times because your joining to an attributes table. A customer usually has many attributes so that join will have them multiple times.  
Yes.

>>>Think we first step back and see what your issue really is.
Issue is the CTE 'customerDups' has duplicates as a result of
CUSTOMER table LEFT OUTER JOIN with CUSTOMER_ATTRIBUTE table.

I dont want to touch the Customer table, that is the master table.
I only want to delete data from CTE customerDups.

thanks.

Again you cant delete from CTE.

You have to Modify it so it displays what you want.

Did you try my last query?

>>Again you cant delete from CTE.
Not entirely true since i was able to delete earlier.
The problem is since i am joining with multiple tables, its not letting me delete
for example this would work fine

;with customerDups as (  
SELECT *,
  ROW_NUMBER() OVER(PARTITION BY customer.CUSTOMERID ORDER BY customer.CUSTOMERID) as customerRank
  FROM LM.Customer customer
  )
DELETE FROM customerDups where customerRank > 1;
-- works fine.

>>Did you try my last query?
yes this is a good alternative. thanks.

That delete works yes and it deletes from the customer table. I told you earlier to remove the join but you said you need it.

The reason that delete works is because the underlying table is only 1. An that query although the statement deletes from the temp table. You may want to see is the deletes are falling off Customer table.

Non the less yours didn't work because you introduced a Join now the engine does not know how to handle the delete.