Running a SQL delete query in Laravel

Question

I have an SQL query:

DELETE n1 
  FROM satellites n1
     , satellites n2 
 WHERE n1.id < n2.id 
   AND n1.norad_cat_id = n2.norad_cat_id

What this query does is delete rows that have the same norad_cat_id and only leave one with the highest id. I don't know if my SQL query is correct, but I will have to see.

I am a bit stuck when it comes to running raw SQL queries in Laravel. From this documentation (https://laravel.com/docs/5.4/database#running-queries) you can see that you have a few options to run the query:

DB::update('SQL QUERY HERE');

DB::delete('SQL QUERY HERE');

DB::statement('SQL QUERY HERE');

DB::select( DB::raw('SQL QUERY HERE'));

In my case I am trying to delete duplicate rows while only leaving the one with the highest id. What Laravel DB statement do I run to achieve the results I want or does it matter at all?

EDIT: SQL query for @MasudMiah

delete satellites from satellites inner join ( select max(id) as lastId, norad_cat_id from satellites group by norad_cat_id having count(*) > 1) duplic on duplic.norad_cat_id = satellites.norad_cat_id where satellites.norad_cat_id < duplic.lastId;

(collected) $deleteDuplicates = DB::table('questions as n1') ->join('questions as n2', 'n1.id', '>', 'n2.id') ->where('n1.name', '=', 'n2.name') ->delete(); The query might look like this: DELETE n1 FROM questions n1, questions n2 WHERE n1.id > n2.id AND n1.name = n2.name — Masud Miah
– Masud Miah, Commented Jul 30, 2017 at 16:47
@MasudMiah That did not work - no error warnings or anything. — D. 777KLM
– D. 777KLM, Commented Jul 30, 2017 at 16:51
@MasudMiah The SQL query I posted above works, but it deletes all the rows that have the same norad_cat_id. I need one left that has highest id — D. 777KLM
– D. 777KLM, Commented Jul 30, 2017 at 16:55
can you change this line where satellites.norad_cat_id < duplic.lastId; to this where satellites.norad_cat_id > duplic.lastId; i am not sure though — Masud Miah
– Masud Miah, Commented Jul 30, 2017 at 17:00

CoursesWeb · Accepted Answer · 2017-07-30 15:45:16Z

12

If you want to run directly your DELETE SQL query, you can use:

$nrd = DB::delete('SQL QUERY HERE');

It returns the number of affected /deleted rows. See this page:

http://coursesweb.net/laravel/working-mysql-database#anc_rsq

edited Jul 30, 2017 at 15:45

answered Jul 30, 2017 at 15:38

CoursesWeb

4,2573 gold badges24 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CoursesWeb Over a year ago

Try this sql:

DELETE FROM satellites WHERE norad_cat_id NOT IN  (SELECT *    FROM (SELECT MAX(n.norad_cat_id) FROM satellites n    GROUP BY n.norad_cat_id) x  )

D. 777KLM Over a year ago

That did not work unfortunately. There is no error code. It just freezes.

CoursesWeb Over a year ago

I tested the following query, and it worked. Try test it in PhpMyAdmin: DELETE FROM satellites WHERE id NOT IN (SELECT * FROM (SELECT MAX(n.id) FROM satellites n GROUP BY n.norad_cat_id) x)

D. 777KLM Over a year ago

It works! Just tested it and confirm that it does what I want it to do! I just need to put it into a DB statement.

Masud Miah · Accepted Answer · 2017-07-30 16:31:58Z

11

I am afraid your query is not right though. but let me show you some :

  DELETE FROM table1 WHERE user_id='$your_provided_value';
    DELETE FROM table2 WHERE user_id='$your_provided_value';

Now using query builder for laravel :

DB::table('table_name')
->where('id',$your_provided_value)
->delete();

One thing I would like to mention to set multiple conditions like id = 1 AND gender = 'male' you need to something like that

DB::table('table_name')->where('id',1)
->where('gendar','male')
->delete();

Now by eloquent :

User:where('id', 1)->delete();

here User is your model for the users table. Hope you are getting some basics. by visiting below link you get the idea of using eloquent. https://scotch.io/tutorials/a-guide-to-using-eloquent-orm-in-laravel

answered Jul 30, 2017 at 16:31

Masud Miah

2561 silver badge5 bronze badges

1 Comment

D. 777KLM Over a year ago

I was trying to make a query which was unsuccessful. Since my database updates I would need it to remove all duplicates. Your code needs $your_provided_value - which in my case I cannot get since it needs to be dynamic. Please see my updated query. The updated query is able to delete all for the rows with the same norad_cat_id, but I need one left.

Collectives™ on Stack Overflow

Running a SQL delete query in Laravel

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related