在Laravel中运行SQL删除查询 [英] Running a SQL delete query in Laravel

问题描述

我有一个SQL查询:

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

此查询的作用是删除具有相同norad_cat_id的行，而只保留具有最高id的行.我不知道我的SQL查询是否正确，但是我必须看看.

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.

在Laravel中运行原始SQL查询时，我有些困惑.从本文档( https://laravel.com/docs/5.4/database#running-查询)，您可以看到有一些选项可以运行查询:

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'));

就我而言，我试图删除重复的行，而只保留具有最高id的行.我运行什么Laravel DB语句来获得所需的结果，或者根本不重要?

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?

编辑:@ MasudMiah的SQL查询

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;

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;

推荐答案

如果要直接运行DELETE SQL查询，可以使用:

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

这篇关于在Laravel中运行SQL删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！