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

    Join Date
    Jul 2003
    Posts
    23
    Rep Power
    0

    need help with an update query


    I have two db that I need to copy fields from one table to another. I only need a couple of fields from one to another.
    The table that I need the info from has only two columns, key and value. I can get the value I need from this table with
    the following query.

    Code:
    select value from asterisk.sysadmin_options where `key` like 'provispass'
    I need to insert that result into cqadmin.global field provispass.

    The field name in cqadmin.global is provispass and provisuser and the Key entreis in sysadmin_options Key are
    the same , provisuser, and provispass

    So do I form some kind of inner join? Both tables have only one entry per column and that will never change.

    Thanks for any ideas
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,774
    Rep Power
    4289
    a bit confused here, could you please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    23
    Rep Power
    0
    Originally Posted by r937
    a bit confused here, could you please do a SHOW CREATE TABLE for each table
    Is this what you are looking for?

    Code:
    CREATE TABLE IF NOT EXISTS `global` (
      `id` int(3) NOT NULL AUTO_INCREMENT,
      `serverlocalip` varchar(25) NOT NULL,
      `serverwanIP` varchar(25) NOT NULL,
      `timeserver1` varchar(35) NOT NULL,
      `timeserver2` varchar(35) NOT NULL,
      `timezone` varchar(5) NOT NULL,
      `userloginpwd` varchar(10) NOT NULL,
      `adminloginpwd` varchar(10) NOT NULL,
      `vlanlanenabled` int(1) NOT NULL,
      `vlanpcenabled` int(1) NOT NULL,
      `vlanlanid` int(5) NOT NULL,
      `vlanpcid` int(5) NOT NULL,
      `vlanlanpriority` int(2) NOT NULL,
      `vlanpcpriority` int(2) NOT NULL,
      `mcpbarge` varchar(4) NOT NULL,
      `mcpignorednd` varchar(4) NOT NULL,
      `alertinfo1` varchar(20) NOT NULL,
      `alertinfo2` varchar(20) NOT NULL,
      `alertinfo3` varchar(20) NOT NULL,
      `alertinfo4` varchar(20) NOT NULL,
      `alertinfo5` varchar(20) NOT NULL,
      `alertinfo6` varchar(20) NOT NULL,
      `alertinfo7` varchar(20) NOT NULL,
      `alertinfo8` varchar(20) NOT NULL,
      `alertinfo9` varchar(20) NOT NULL,
      `alertinfo10` varchar(20) NOT NULL,
      `dst` int(1) NOT NULL,
      `a11fw` varchar(30) NOT NULL,
      `a11md5` varchar(30) NOT NULL,
      `a20fw` varchar(30) NOT NULL,
      `a20md5` varchar(30) NOT NULL,
      `a40fw` varchar(30) NOT NULL,
      `a40md5` varchar(30) NOT NULL,
      `a68fw` varchar(30) NOT NULL,
      `a68md5` varchar(30) NOT NULL,
      `provisuser` varchar(30) NOT NULL,
      `provispass` varchar(30) NOT NULL,
      `provisurl` varchar(100) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    --

    Code:
    -- Table structure for table `sysadmin_options`
    --
    
    CREATE TABLE IF NOT EXISTS `sysadmin_options` (
      `key` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
      `value` text COLLATE utf8_unicode_ci,
      UNIQUE KEY `key` (`key`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,774
    Rep Power
    4289
    yup, that helps, i'm not sure why i was confused
    Code:
    UPDATE global
       SET provispass =
           ( SELECT value 
               FROM sysadmin_options 
              WHERE `key` = 'provispass' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    23
    Rep Power
    0
    Thank you very much! Works just as I needed it.!

    Originally Posted by r937
    yup, that helps, i'm not sure why i was confused
    Code:
    UPDATE global
       SET provispass =
           ( SELECT value 
               FROM sysadmin_options 
              WHERE `key` = 'provispass' )

IMN logo majestic logo threadwatch logo seochat tools logo