Professional Web Applications Themes

UPDATE table2 using table1 data - MySQL

Using 4.0.20a-nt on Win2K, but the query will eventually be run on a Linux box (not sure what MySQL version). I'm trying to pull DISTINCT records out of one table and populate another table with that data. table2 is a structural copy of table1. So I do: SELECT * FROM table1 WHERE 1=0 to get table2. Now I need to pull out distinct records according to email from table1, but populate table2 with ALL of the columns that match the DISTINCT query. I'm doing this in a Perl script as a nested pair of queries like this: $Q1 = qq{SELECT ...

  1. #1

    Default UPDATE table2 using table1 data

    Using 4.0.20a-nt on Win2K, but the query will eventually be run on a
    Linux box (not sure what MySQL version).

    I'm trying to pull DISTINCT records out of one table and populate
    another table with that data.

    table2 is a structural copy of table1.

    So I do:
    SELECT * FROM table1 WHERE 1=0
    to get table2.

    Now I need to pull out distinct records according to email from table1,
    but populate table2 with ALL of the columns that match the DISTINCT
    query.

    I'm doing this in a Perl script as a nested pair of queries like this:

    $Q1 = qq{SELECT DISTINCT email FROM customers};
    $sth1=$dbh->prepare(qq{$Q1});
    $sth1->execute();

    print "Q1 is\n$Q1\n";

    while (rows1 = $sth1->fetchrow_array())
    {
    $thisemail = $rows1[0];
    print "$thisemail\n";

    $Q2 = qq{
    UPDATE newcustomers
    SET newcustomers.custid=customers.custid,
    newcustomers.firstname=customers.firstname,
    newcustomers.lastname=customers.lastname,
    newcustomers.address1=customers.address1,
    newcustomers.address2=customers.address2,
    newcustomers.city=customers.city,
    newcustomers.province=customers.province,
    newcustomers.postalcode=customers.postalcode,
    newcustomers.areacode=customers.areacode,
    newcustomers.exchange=customers.exchange,
    newcustomers.phone=customers.phone,
    newcustomers.extension=customers.extension,
    newcustomers.email=customers.email
    WHERE customers.email = '$thisemail'};

    print "Q2 is:\n$Q2\n";

    $inserts=$dbh->do(qq{$Q2});
    }
    $sth1->finish();
    print "$inserts records modified\n";

    This results in an error:
    DBD::mysql::db do failed: Unknown table 'customers' in where clause at
    get_cust01.pl line 56.
    Line 56 ==> '$inserts=$dbh->do(qq{$Q2});'

    Is it possible to do what I'm trying to do? I can't believe it isn't.
    If someone can shed some light for me, I'd appreciate it.

    Trudge Guest

  2. #2

    Default Re: UPDATE table2 using table1 data

    Yeap, it's possible, but it looks like you are trying the wrong way.
    Shortly, I say you have to read about mulitable updates that are
    avaliable in mySQL 4.
    [url]http://dev.mysql.com/doc/refman/4.1/en/update.html[/url]

    If the only thing you want to do is fetch all distinct rows from
    customers and put them into newcustomers then you can issue the
    following query

    mysql> truncate table newcustomers;
    mysql> insert into newcustomers select distinct * from customers;

    Actually, it looks like you want to get unique emails from customers
    and fill newcustomers with unique emails only, right? So, other words,
    you simply want to eliminate customers duplication and relay on email
    as primary key, right?
    If so, then you can do like this:
    1. create newcustomers with unique emails and min(custid)
    create temporary table uniq_custs as select min(custid) custid, email
    from customers group by email;
    2. insert into newcustomers select customers.* from customers inner
    join uniq_custs using (custid);

    That's all!

    robocomp Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. keep data update
    By jose cardona in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: April 25th, 11:42 PM
  3. Replies: 2
    Last Post: March 8th, 11:57 AM
  4. update data on datagrid and DB
    By Grey in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 14th, 02:25 PM
  5. update data on form
    By Chris in forum Microsoft Access
    Replies: 3
    Last Post: July 27th, 11:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139