CODEWITHSUNDEEP
mysqlgroup-byleft-join
Arnas Pecelis
Arnas Pecelis

Reputation: 149

Deleting rows with LEFT join and GROUP BY

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

Upvotes: 2

Views: 266

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 1

Related Questions