I'm trying to update a column with the value 1 where the following criteria match.
UPDATE
(SELECT TBL.KEY, TBL.IS_DUPLICATE FROM MYTABLE AS TBL JOIN
(SELECT TBL1.KEY FROM MYTABLE AS TBL1 GROUP BY TBL1.KEY HAVING COUNT(TBL1.KEY)>1) SELECTION
ON SELECTION.KEY = TBL.KEY ORDER BY TBL.KEY ASC) OuterSelection
SET OuterSelection.IS_DUPLICATE = 1;
Here is the error I'm getting when running this query.
SQL Error [42807]: The target fullselect, view, typed table, materialized query table, range-clustered table, or staging table in the INSERT, DELETE, UPDATE, MERGE, or TRUNCATE statement is a target for which the requested operation is not permitted.. SQLCODE=-150, SQLSTATE=42807, DRIVER=4.16.53
Problem was that the selection had to be matched in the WHERE clause, and the UPDATE should just specify the table. Here is the solution:
UPDATE TBL
SET IS_DUPLICATE=1
WHERE KEY IN (SELECT KEY
FROM TBL
GROUP BY KEY
HAVING COUNT(*) > 1);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments