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?
 
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
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
royjaydAuthor Commented:
hi Leo,
I need the join with the LM.CUSTOMER_ATTRIBUTE table.
thx
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.