#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    8
    Rep Power
    0

    Updating records from one table to another were matching.


    HI All

    I'm new to this forum.

    I have two tables.

    Table one

    Code:
    CREATE TABLE `BEN` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `dt` datetime DEFAULT NULL,   `dttext` tinytext,   `ticketnum` tinytext,   `accnum` tinytext,   `lname` tinytext,   `sname` tinytext,   PRIMARY KEY (`id`),   UNIQUE KEY `idxunique` (`dt`,`dttext`(255),`ticketnum`(255),`accnum`(255),`lname`(255),`sname`(255)),   KEY `idxticketnum` (`ticketnum`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=27594782 DEFAULT CHARSET=latin1;
    Table two

    Code:
    CREATE TABLE `commlogin` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `profid` bigint(20) unsigned DEFAULT NULL,   `box` tinytext,   `batch` tinytext,   `userid` bigint(20) unsigned DEFAULT NULL,   `dt` datetime DEFAULT NULL,   `idx1t` tinytext,   `idx2t` tinytext,   `idx3t` tinytext,   `idx4t` tinytext,   `idx5t` tinytext,   `idx1dt` datetime DEFAULT NULL,   `idx2dt` datetime DEFAULT NULL,   `idx3dt` datetime DEFAULT NULL,   `idx4dt` datetime DEFAULT NULL,   `idx5dt` datetime DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idxprofid` (`profid`),   KEY `idxbatch` (`batch`(255)),   KEY `idxuserid` (`userid`),   KEY `idxidx1t` (`idx1t`(255)),   KEY `idxidx2t` (`idx2t`(255)),   KEY `idxidx3t` (`idx3t`(255)),   KEY `idxidx4t` (`idx4t`(255)),   KEY `idxidx5t` (`idx5t`(255)),   KEY `idxidx1dt` (`idx1dt`),   KEY `idxidx2dt` (`idx2dt`),   KEY `idxidx3dt` (`idx3dt`),   KEY `idxidx4dt` (`idx4dt`),   KEY `idxidx5dt` (`idx5dt`),   KEY `idxbox` (`box`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=6717310 DEFAULT CHARSET=latin1;
    Now I would like for the Commlogin table to update all the records from Ben table.
    So basically it will some how have to find a match of Commlogin.idx1 = ben.ticketnum then in it pulls information for ben and addes it to Commlogin, like dttext will pull into idx2, accnum will pull into idx3?

    How would i do this?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Blackghost
    How would i do this?
    use a joined update

    there is an example in da manual under the syntax for the UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    use a joined update

    there is an example in da manual under the syntax for the UPDATE statement
    HI @r937

    Thank you for the response

    I should of told you that I am not the greatest in mysql the guy that does in is in the hospital and i need to get this done?

    Please will you point me in the correct way in how to right this script?

    Thank you

    just did some reading, is the insert into not a better way to go?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Blackghost
    Please will you point me in the correct way in how to right this script?
    Originally Posted by da manual
    You can also perform UPDATE operations covering multiple tables. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.8.2, “JOIN Syntax”. Here is an example:
    Code:
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;
    The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.

    -- http://dev.mysql.com/doc/refman/5.0/en/update.html
    Originally Posted by Blackghost
    just did some reading, is the insert into not a better way to go?
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    no
    thank you, will work on this for now

IMN logo majestic logo threadwatch logo seochat tools logo