CODEWITHSUNDEEP
sqlgoogle-bigquery
HermanK
HermanK

Reputation: 309

Delete from main table records that apear in cte

I have a main table from which I need to delete rows that are returned in CTE Preferably I would like to do something like that:

with users_to_exclude as (
select uid, date, location_id
from some_previous_ctes_with_logic
)
delete from main_table mt
inner join 
users_to_exclude ux
 on ux.uid = mt.uid
 and ux.date = mt.date
 and ux.location_id = mt.location_id

But I have not idea how to approach this in GBQ. If I had only one key by which I would delete, it would be simpler as I would use all the CTE with a final select field in the where clause. But where (f1,f2,f3) in (select f1,f2,f3 from..) is not possible in GBQ to my understanding.

So not sure how to do that.

Thanks

Upvotes: 1

Views: 332

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Consider below approach

delete from main_table mt
where exists (
  with users_to_exclude as (
    select uid, date, location_id
    from some_previous_ctes_with_logic
  )
  select 1 from users_to_exclude ux
  where ux.uid = mt.uid
  and ux.date = mt.date
  and ux.location_id = mt.location_id
);

why do I have to attach it to the main table in the where clause?
If for some reason you want to go this direction - use below

delete from main_table mt
where (uid, date, location_id) in (
  with users_to_exclude as (
    select uid, date, location_id
    from some_previous_ctes_with_logic
  )
  select as struct  * from users_to_exclude
);

Upvotes: 1

Related Questions