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? Thanks you response.

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

2

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,317
4
26
50

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

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

Laravel DB::table update filed

3 Answers3