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. 'title_name' => \DB::raw("REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title_name, 'MR.', 'mr'), 'MRS.', 'mrs'), 'Miss', 'ms'),'Girl', 'girl'), 'Boy','boy')") — Ngoc Nam
– Ngoc Nam, Commented Oct 18, 2018 at 5:26

Mr. Pyramid · Accepted Answer · 2018-10-18 07:31:38Z

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, 2018 at 7:31

answered Oct 18, 2018 at 5:30

Mr. Pyramid

3,9355 gold badges35 silver badges57 bronze badges

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

3 Comments

Teeraparb Charoenwasusup Over a year ago

Im' sorry,I got Undefined offset: 5

Teeraparb Charoenwasusup Over a year ago

l'm sorry,If use strtolower can't remove dot after mr. mrs. ,but thanks sir

Mr. Pyramid Over a year ago

check the updated answer it will remove dot from string

Hicham O'Sfh · Accepted Answer · 2021-12-13 11:43:41Z

1

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"]);

answered Dec 13, 2021 at 11:43

Hicham O'Sfh

84912 silver badges14 bronze badges

Comments

localroot · Accepted Answer · 2018-10-18 06:40:10Z

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, 2018 at 6:40

answered Oct 18, 2018 at 5:15

localroot

5563 silver badges13 bronze badges

2 Comments

localroot Over a year ago

Just updated, try again and paste ther exact error message if have any issue.

Teeraparb Charoenwasusup Over a year ago

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)

Nitheesram Rajes · Accepted Answer · 2018-10-18 05:41:37Z

0

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

answered Oct 18, 2018 at 5:41

Nitheesram Rajes

1541 silver badge15 bronze badges

Collectives™ on Stack Overflow

Laravel DB::table update filed

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related