Delete record with same value in different column only once

Question

Delete record with same value in different column only once

Navigation

#1 by (0 votes)

0

Good morning, in a trigger for insert I need to delete all the records that have a value greater than 0 in vat and at the same time delete one that does not say vat of the same client and same rut. (Does not have primary key)

EJ:

Cliente | rut        | precio | ganado | iva    | fecha
────────┼────────────┼────────┼────────┼────────┼─────────
Diego   | 23423333-3 | 345344 | 345344 | 0      | 20160111
Diego   | 23423333-3 | 345344 | 345344 | 0      | 20160111
Diego   | 23423333-3 | 345344 | 345344 | 0      | 20160111
Diego   | 23423333-3 | 345344 | 0      | 345344 | 20160111

delete top (
  SELECT COUNT(*)
  FROM [dbo] as f 
  inner join inserted as d on (d.rut=f.rut)
  Where f.ganado in(select iva from Inserted) and f.nombre=d.nombre and d.fecha=f.fecha
)
from [dbo] 
from [dbo] as f
inner join inserted as d on (d.rut=f.rut)
Where f.ganado in(select iva from Inserted) and f.nombre=d.nombre and d.fecha=f.fecha

SHOULD: Delete the row with VAT and one with same rut, name date and price won.

EJ:

Cliente | rut        | precio | ganado | iva | fecha
────────┼────────────┼────────┼────────┼─────┼─────────
Diego   | 23423333-3 | 345344 | 345344 | 0   | 20160111
Diego   | 23423333-3 | 345344 | 345344 | 0   | 20160111

But it only eliminates value without iva, leaving the records gained without eliminating them (from 1 to 1).

Thank you very much already.

sql tsql trigger

asked by DiegoDW 23.03.2016 в 14:31

source

1 answer

Run FindVisualChildren several times in WPF error javax.ejb.EJBException

score 0 · Accepted Answer

DECLARE @ruc NVARCHAR(32), @Nombre  NVARCHAR(32), @fecha   NVARCHAR(32), @iva decimal , @ganado decimal  ,@precio decimal

 /*Eliminamos los registros con IVA mayor a 0*/
SELECT @ruc = rut, @Nombre = nombre, @fecha = fecha , @iva = iva, @ganado  = ganado    , @precio = precio
FROM inserted

DELETE FROM  dbo
WHERE nombre = @Nombre AND
      rut    = @ruc   AND
      iva    > 0


/*Para el paso dos depende. En sql server. La información ya esta insertada. 
  Por lo que al eliminar, eliminarias tambien el registro insertado. Debes distinguir los
  registros con una clave unica. Asi podrias aplicar esto*/

  DELETE FROM  dbo
WHERE nombre = @Nombre AND
      rut    = @ruc    AND
      ganado = @ganado AND
      precio = @precio AND
      fecha  = @fecha  AND
      id     < 15       /*Id insertado, eliminamos los registros que sean iguales y conservamos el ultimo insertado*/