New answers tagged

4

You can do this much simpler using a CTE: ;WITH x AS ( SELECT Field1, Field2, c = COUNT(*) OVER(PARTITION BY Field1, Field2) FROM dbo.Table1 WHERE Field2 <> '' ) UPDATE x SET Field2 = '' WHERE c > 1; Or with a more cumbersome (and potentially less efficient, did not test) self-join: UPDATE t SET Field2 = '' FROM dbo.Table1 AS ...


1

Found the answer. Use a temp table and then inner join: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) AS Counted INTO #TempTable FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY [t].[Field1], [t].[Field2] HAVING COUNT([t].[Field2]) > 1 UPDATE [dbo].[Table1] SET [Field2] = '' FROM [dbo].[Table1] AS [t] ...


0

Could you please tell us how you connected to rman? For example: rman target sys/asdf@prod auxiliary sys/asdf@dev The reason for you problem is that the two databases have trouble communicating with each other. If you use auxiliary / because your rman session is located on the dev server this is not going to work. The reason is pretty simple as follows. ...


0

I'm assuming that you have no foreign key constraints on the phone column and you have a Primary Key defined: DELETE FROM foo_schema.bar_table WHERE primary_key_column NOT IN (SELECT MAX(primary_key_column) FROM foo_schema.bar_table GROUP BY phone) This should group all ...


0

You need Except clause Something like SELECT * FROM first_table EXCEPT SELECT * FROM second_table This return all rows from first table that are not in second table


0

This will work I think: with d as ( select ctid, row_number() over (partition by t.*) as rn from tablename as t ) delete from tablename as t using d where d.rn > 1 and d.ctid = t.ctid ; And another variation. Not sure which will be more efficient: delete from tablename as t where exists ( select * from tablename as d ...



Top 50 recent answers are included