How to update a table from a select query

Question

i am creating a temp table using DB facade and then using select query i need to update some columns in temp table based on condition

DB::update('update table_temp_topcustomer 
            set ordercount = aaa.ordercount 
            from 
            (select count(id) as ordercount,mobileno 
             from order_hdrs 
             group by mobileno 
            ) as aaa
            where table_temp_topcustomer .mobileno = aaa.mobileno
          ');

it gives this error

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from (select count(id) as ordercount,mobileno from order_hdrs group by mobileno ' at line 1 (SQL: update table_temp_topcustomer set ordercount = aaa.ordercount from (select count(id) as ordercount,mobileno from order_hdrs group by mobileno ) as aaa )

How can i achieve this?

score 1 · Answer 1 · answered May 26 '19 at 23:38

UPDATE table_temp_topcustomer JOIN 
        ( SELECT order_hdrs count(*) as ordercount 
              FROM order_hdrs 
              GROUP BY mobileno
        ) AS aaa 
           ON table_temp_topcustomer.mobileno = aaa.mobileno
        SET   table_temp_topcustomer.ordercount = aaa.ordercount

vijaykumar · Answer 2 · 2019-05-21T08:25:43.130

0

I guess you can't do in single query. As per my understanding. First get the select result and in loop do update.

You should try something like below.

$result = DB::select('select count(id) as ordercount,mobileno 
                 from order_hdrs 
                 group by mobileno');
foreach($result as $item) {

    DB::update('update table_temp_topcustomer 
               set ordercount = '. $item->ordercount .' 
              where table_temp_topcustomer.mobileno = ' $item->mobileno);
}

edited May 21 '19 at 08:25

answered May 21 '19 at 05:30

vijaykumar

4,658
6
37
54

instead of query use select..then works fine. $result1 = DB::select('select count(id) as ordercount,mobileno from order_hdrs group by mobileno'); but since loop will make query slow..is there a better way to achieve ? – Kumar May 21 '19 at 06:03
I don't think so. Just add index for column mobileno to increase the speed. – vijaykumar May 21 '19 at 08:26
"Just add index for column mobileno to increase the speed" i dont understand this..can you please explain ? – Kumar May 22 '19 at 02:15

How to update a table from a select query

2 Answers2