#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171

    UPDATE on SELECT


    Hi;

    This is silly. Can someone please tell me how to do it in 1 hit instead of looping?
    PHP Code:
    class Script extends CI_Controller {
        public function 
    index()
            {
                
    $this->db->select('daily_deal_id, expiry');
                
    $query $this->db->get('packages_daily_deal_vouchers');
                foreach (
    $query->result() as $row)
                    {
                        
    $data = array('expiry_date' => $row->expiry);
                        
    $this->db->where('daily_deal_id'$row->daily_deal_id);
                        
    $this->db->update('packages_daily_deal_voucher_numbers'$data);
                    }
            }    

    Thanks
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    The manual: UPDATE syntax, multi-table syntax.

    sql Code:
    UPDATE
    	packages_daily_deal_voucher_numbers
    	JOIN packages_daily_deal_vouchers USING (daily_deal_id)
    SET
    	packages_daily_deal_voucher_numbers.expiry_date = packages_daily_deal_vouchers.expiry
    ;

    But why do you want to do this, anyway? Regularly copying the complete column of one table into another table sounds rather obscure.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Originally Posted by Jacques1
    But why do you want to do this, anyway?
    Hi;

    Because that is the only way I know. What you have done above with "USING" is not something I am familiar with.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    No, I mean why do you want to do this update at all? Why do you want to copy the values from packages_daily_deal_vouchers into packages_daily_deal_voucher_numbers as opposed to, say, using references?

    The table_1 JOIN table_2 USING (colum) syntax is just a short way of saying table_1 JOIN table_2 ON table_1.colum = table_2.colum.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Originally Posted by Jacques1
    No, I mean why do you want to do this update at all? Why do you want to copy the values from packages_daily_deal_vouchers into packages_daily_deal_voucher_numbers as opposed to, say, using references?
    Hi;

    I am sure you have figured this out.

    There is a table with thousands of vouchers. They now need to have an expiry date.

    Each of these vouchers have a "categoty".

    Each category has an "expiry date".

    Using this JOIN I get the expiry date and UPDATE.

    Your solutions are very modern compare to classic code.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    Well, one thing I will toss in, which as always, does have its exceptions, but as far as databases go, I would recommend to try your hardest to NOT have duplicate data entered throughout the database. This makes updating things in the future a bit more a hassle and all. As Jacques1 has mentioned, try to refence the best you can. If you already have 'categories' defined with expiration dates, then if your coupon doesn't already have a column defining its category, make that. And as an example for future ease, if you needed to change an expiration date for perhaps 20 coupons in a certain category, this is now just 1 item versus each and every coupon.

    The command you may wish to look more into would be the JOIN syntax. Roughly speaking, what this will do is take your main table initially defined, and attach the second table to it, just for this query, so that column is now part of the same table as needed.

    EDIT: I think my response may be dismissed. I feel I may have totally overlooked/misunderstood the issue.

    EDIT2: Got myself on track. Sorry. You told us you have coupons, coupons have categories, categories hold expire dates. This is all you need. don't add more. You do have the idea you need down, as far as JOIN, just don't use it for your UPDATE. Use it when fetching a coupon and needed the expiry date.
    Last edited by Triple_Nothing; November 7th, 2013 at 11:08 AM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him

IMN logo majestic logo threadwatch logo seochat tools logo