Laravel DB::table update filed

Question

I have this query

DB::table('pro_orders_has_passengers')
    ->where('title_name','MR.')
    ->update([
        'title_name' => 'mr',
    ]);
DB::table('pro_orders_has_passengers')
    ->where('title_name','MRS.')
    ->update([
        'title_name' => 'mrs',
    ]);
DB::table('pro_orders_has_passengers')
    ->where('title_name','Miss')
    ->update([
        'title_name' => 'ms',
    ]);
DB::table('pro_orders_has_passengers')
    ->where('title_name','Girl')
    ->update([
        'title_name' => 'girl',
    ]);
DB::table('pro_orders_has_passengers')
    ->where('title_name','Boy')
    ->update([
        'title_name' => 'boy',
    ]);

I got the right result. But I want this result in one query.

Something like this:

$titlename = ['MR.','MRS.','Miss','Girl','Boy'];

DB::table('pro_orders_has_passengers')
    ->where('title_name',$titlename)
    ->update([
        ........
    ]);

How can i do this?

You can try [REPLACE](https://stackoverflow.com/a/14586441). `'title_name' => \DB::raw("REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title_name, 'MR.', 'mr'), 'MRS.', 'mrs'), 'Miss', 'ms'),'Girl', 'girl'), 'Boy','boy')")` — Ngoc Nam, Oct 18 '18 at 05:26

Mr. Pyramid · Answer 1 · 2018-10-18T07:31:38.397

4

I assume this way you can achieve what you are looking for

$titlename = ['MR.','MRS.','Miss','Girl','Boy'];
for($i=0; $i < count($titlename);$i++) {
DB::table('pro_orders_has_passengers')
            ->where('title_name',$titlename[$i])
            ->update(['title_name' => strtolower(str_replace('.', '' , $titlename[$i]))]);
}

Or with a foreach loop, for improved code readability:

$titlenames = ['MR.','MRS.','Miss','Girl','Boy'];
foreach($titlenames as $titlename) {
DB::table('pro_orders_has_passengers')
            ->where('title_name',$titlename)
            ->update(['title_name' => strtolower(str_replace('.', '' , $titlename))]);
}

edited Oct 18 '18 at 07:31

answered Oct 18 '18 at 05:30

Mr. Pyramid

3,855
5
32
56

Im' sorry,I got Undefined offset: 5 – Teeraparb Charoenwasusup Oct 18 '18 at 06:13
l'm sorry,If use strtolower can't remove dot after mr. mrs. ,but thanks sir – Teeraparb Charoenwasusup Oct 18 '18 at 06:23
check the updated answer it will remove dot from string – Mr. Pyramid Oct 18 '18 at 07:32

localroot · Accepted Answer · 2018-10-18T06:40:10.510

0

    $titlename = ['mr' => 'MR.','mrs' => 'MRS.','ms' => 'Miss','girl' => 'Girl', 'boy' => 'Boy'];

    $update_query = "UPDATE pro_orders_has_passengers SET title_name = CASE";
    foreach ($titlename  as $new_title => $title) {
       $update_query = $update_query . " WHEN title_name = '$title'  THEN  '$new_title'";
    }
    $update_query = $update_query . " END WHERE title_name IN ('".implode(array_values($titlename), '\',\'')."')";
    \DB::statement($update_query);

Try this way. It'll help you to do bulk update.

Final ouput will be

UPDATE pro_orders_has_passengers SET title_name = CASE WHEN title_name = 'MR.' THEN 'mr' WHEN title_name = 'MRS.' THEN 'mrs' WHEN title_name = 'Miss' THEN 'ms' WHEN title_name = 'Girl' THEN 'girl' WHEN title_name = 'Boy' THEN 'boy' WHERE END title_name IN ('MR.','MRS.','Miss','Girl','Boy')

edited Oct 18 '18 at 06:40

answered Oct 18 '18 at 05:15

localroot

566
3
13

Just updated, try again and paste ther exact error message if have any issue. – localroot Oct 18 '18 at 06:21
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual th at corresponds to your MariaDB server version for the right syntax to use near 'WHERE title_name IN ('MR.','MRS.' ,'Miss','Girl','Boy') END' at line 1 (SQL: UPDATE pro_orders_has_passengers SET title_name = CASE WHEN title_name = 'MR.' THEN 'mr' WHEN title_name = 'MRS.' THEN 'mrs' WHEN title_name = 'Miss' THEN 'ms' WHEN title_name = 'Girl' THEN 'girl' WHEN title_name = 'Boy' THEN 'boy' WHERE title_name IN ('MR.','MRS.','Miss','Girl','Boy') END) – Teeraparb Charoenwasusup Oct 18 '18 at 06:35

score 0 · Answer 3 · answered Oct 18 '18 at 05:41

Try This one. For more refer following links

1.I want to use CASE statement to update some records in sql server 2005

2.Running A General Statement : https://laravel.com/docs/5.7/database

DB::statement(
      UPDATE pro_orders_has_passengers
      SET title_name = CASE  
                    WHEN title_name = 'Mr.' THEN 'mr' 
                    WHEN title_name = 'Mrs.' THEN 'mrs' 
                    //-------Others cases-----//
                    ELSE LASTNAME
                END 
      WHERE title_name IN ('Mr.', 'Mrs.', .....)
);

score 0 · Answer 4 · answered Dec 13 '21 at 11:43

You can update it doing :

DB::table('table_1')
                ->join("table_2", "table_1.foreign_id", "=", "table_2.id")
                ->where("table_1.fied_test", "=", "a_condition")
                ->where("table_2.user_id", "=", auth()->id())
                ->update(["table_2.status_test" => "EN_COURS"]);

Laravel DB::table update filed

4 Answers4