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

Dear experts

This is my sql
;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 customer.ISACTIVE='T'
)
DELETE FROM customerDups where customerRank > 1;

Open in new window


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;

But i end up getting this error:
View or function 'customerDups' is not updatable because the modification affects multiple base tables.

Any idea how i can resolve this?

Thanks
royjaydAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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;
0
Leo TorresSQL DeveloperCommented:
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;

Open in new window

0
royjaydAuthor Commented:
hi Leo,
I need the join with the LM.CUSTOMER_ATTRIBUTE table.
thx
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Leo TorresSQL DeveloperCommented:
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;

Open in new window

0
royjaydAuthor Commented:
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;
0
Leo TorresSQL DeveloperCommented:
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;

Open in new window

0
Leo TorresSQL DeveloperCommented:
Since this is a delete please to a
Select * into TableBackup
from DeleteTable.

Just to be safe.
0
royjaydAuthor Commented:
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'.
0
royjaydAuthor Commented:
i think it should be >>DELETE customer FROM LM.Customer customer
right ?
0
Leo TorresSQL DeveloperCommented:
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;

Open in new window

0
royjaydAuthor Commented:
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.
0
Leo TorresSQL DeveloperCommented:
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

Open in new window

0
royjaydAuthor Commented:
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 .
0
Leo TorresSQL DeveloperCommented:
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'
)

Open in new window


You cant delete from your CTE.
0
Leo TorresSQL DeveloperCommented:
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.
0
Leo TorresSQL DeveloperCommented:
What you may need is Customer only once then

;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 customer.ISACTIVE='T'
)
Select * FROM customerDups where customerRank = 1;

Open in new window


In this CTE there should not be any dups
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
royjaydAuthor Commented:
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.
0
Leo TorresSQL DeveloperCommented:
Again you cant delete from CTE.

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

Did you try my last query?
0
royjaydAuthor Commented:
>>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.
0
Leo TorresSQL DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.