- Query Syntax
- SQL
- Microsoft SQL Server 2008
- Microsoft SQL Server
sql rank <<<not updatable because the modification affects multiple base tables>>
Dear experts
This is my sql
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
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;
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
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;
...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?
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;
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;
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;
>>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;
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'.
INNER JOIN customerDups cd
on cd.SomeKEY = customer.SomeKey
where customerRank > 1;
error is
>>>Incorrect syntax near 'customer'.
Yes remove the alias you cant have alias in a delete
try
try
DELETE FROM LM.Customer
Join customerDups
on customerDups.SomeKEY = Customer.SomeKey
where customerRank > 1;
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.
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:
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
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 .
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
You cant delete from your CTE.
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'
)
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.
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.
What you may need is Customer only once then
In this CTE there should not be any dups
;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;
In this CTE there should not be any dups
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.
>> 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?
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.
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.
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.
-
![]()
received a Solution
Join Two SQL Tables To Get Latest Date -
![]()
wrote an Article
![]()
How to Attach SQL Database Which is Not Properly Detached
-
![]()
created a Video
![]()
Repair Corrupt SQL Database with Stellar Phoenix SQL Database Repair Software
-
Explore Cloud Class® ![]()
Social Media in the Workplace
Premium Content
You need an Expert Office subscription to comment.Start Free Trial