Deleting rows with LEFT join and GROUP BY

2
votes

I'm trying to delete records on table based on another table its status.

delete from hp_visitors_data
left join hp_programs_list
  on hp_visitors_data.visitor_program_viewed = hp_programs_list.id
where hp_programs_list.program_add_status = 3
group by hp_visitors_data.visitor_program_viewed

But I keep getting an error, what I'm doing wrong?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join hp_programs_list on hp_visitors_data.visitor_program_viewed = hp_pro' at line 2

1
mysqlgroup-byleft-join
A left join in a delete is indeed an odd thing – Strawberry
What is the error that you keep on getting? – Paul Karam
edited, added an error – Arnas Pecelis
remove the group by – Gudgip
In mysql the use of join in a delete is perfectly legal .. – ScaisEdge

1 Answers

1
votes

For a delete you should use an inner join so you work only on the rows that match
anyway you are using a group by without aggregation function but overall you need a table for delete 

  delete hp_visitors_data.*  
  from hp_visitors_data
  INNER  join hp_programs_list
    on hp_visitors_data.visitor_program_viewed = hp_programs_list.id 
      and hp_programs_list.program_add_status = 3