Post reply

delete from one table based on the results of other table

  • May 28, 2015 at 10:18 am

    #398515

    Hi,

    I have this table:

    with actividades_secundarias as (

    select a.*, r.Antigo, r.Novo, rn = row_number()

    over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)

    from ACT_SECUNDARIAS a inner join

    ((select

    a.antigo,

    a.novo

    from

    mergecae2 a)

    union

    (select codigo as antigo, codigo as novo from cae where DESCRICAO not like '%descontinuado%'))r

    on a.COD_CAE = r.Antigo and a.COD_CAE is not null and r.Antigo is not null and r.Novo is not null)

    I want to make a delete statement like this:

    select * into #table1 from actvidades_secundarias where rn>1

    Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae

    But it seems that I cant delete like this. can someone help?

    Thank you

  • May 28, 2015 at 10:27 am

    #1801316

    Quick thought, you can change the select to a delete and delete from the cte directly.

    😎

  • May 29, 2015 at 2:43 am

    #1801497

    lol But how? that is my problem...

  • May 29, 2015 at 3:03 am

    #1801502

    DELETE Table1 FROM Table1 INNER JOIN Table2 ....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 29, 2015 at 3:27 am

    #1801503

    let me try

  • May 29, 2015 at 4:08 am

    #1801514

    Thank you Gail.

    I did it like:

    DELETE ACT_SECUNDARIAS FROM ACT_SECUNDARIAS AS a INNER JOIN actividades_secundarias AS b

    ON a.NIF_ANTIGO = b.NIF_ANTIGO and a.COD_CAE = b.COD_CAE and a.COD_SECTOR = b.COD_SECTOR and a.DT_BEGIN = b.DT_BEGIN

    WHERE b.rn >1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply