I have a table with username, dbname, objecttablename, object type (Table or view). For some data, there are objecttablename that is same for both table and view. i.e the view and table name are same but different databases. If that is the case, I need to remove the duplicate (i.e objecttype view has to be removed) and have only the row of objecttablename and objecttype as table as output. Could anyone please help on this approach?
Create a new table with same structure:
INSERT INTO NEWTABLE
SELECT * FROM OLDTABLE
QUALIFY ROW_NUMBER() OVER(PARTITION BY OBJECTTABLENAME ORDER BY OBJECTTYPE) = 1;