[EMAIL PROTECTED] writes:
> >Description:
> When updating a table after it has been selected with a LEFT JOIN, some
> UPDATE queries execute normally without actually updating the data, unless the
> data to be updated is selected normally first.
> This script is written in mod_perl, using DBI::mysql to connect to the
> database. The following are the tables referenced by the query:
>
> mysql> desc dealer;
> +----------------------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------------------+---------------+------+-----+---------+----------------+
> | dealer_id | int(8) | | PRI | NULL | auto_increment |
> | dealer_code | varchar(5) | | | | |
> | group_id | int(8) | | | 0 | |
> | emp_id | int(8) | | | 0 | |
> | address_id | int(8) | | | 0 | |
> | dealer_name | varchar(200) | | | | |
> | dealer_state | int(8) | | | 0 | |
> | dealer_zip | varchar(5) | | | | |
> | dealer_contact_fname | varchar(100) | YES | | NULL | |
> | dealer_contact_lname | varchar(100) | YES | | NULL | |
> | dealer_URL | varchar(200) | YES | | NULL | |
> | dealer_email | varchar(100) | YES | | NULL | |
> | dealer_password | varchar(100) | YES | | NULL | |
> | dealer_ph1 | varchar(25) | YES | | NULL | |
> | dealer_ph2 | varchar(25) | YES | | NULL | |
> | dealer_fax | varchar(25) | YES | | NULL | |
> | active | enum('Y','N') | | | N | |
> +----------------------+---------------+------+-----+---------+----------------+
> 17 rows in set (0.00 sec)
>
> mysql> desc dealer_lead_method;
> +-------------------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------------+------------+------+-----+---------+-------+
> | dealer_id | int(8) | | PRI | 0 | |
> | method_id | tinyint(4) | | PRI | 0 | |
> | method_parameters | text | YES | | NULL | |
> +-------------------+------------+------+-----+---------+-------+
> 3 rows in set (0.00 sec)
>
> mysql> desc tlkp_dealer_lead_method;
> +-------------+-------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+----------------+
> | method_id | tinyint(4) | | PRI | NULL | auto_increment |
> | method_name | varchar(50) | YES | | NULL | |
> +-------------+-------------+------+-----+---------+----------------+
> 2 rows in set (0.00 sec)
>
> mysql> desc tlkp_address;
> +----------------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------------+--------------+------+-----+---------+----------------+
> | address_id | int(8) | | PRI | NULL | auto_increment |
> | export | int(8) | | | 0 | |
> | address_addr_1 | varchar(200) | YES | | NULL | |
> | address_addr_2 | varchar(200) | YES | | NULL | |
> | address_city | varchar(200) | YES | | NULL | |
> | address_state | int(8) | | | 0 | |
> | address_zip | varchar(5) | YES | | NULL | |
> | address_plus4 | varchar(4) | YES | | NULL | |
> | cdate | int(8) | | | 0 | |
> +----------------+--------------+------+-----+---------+----------------+
> 9 rows in set (0.01 sec)
>
> mysql> desc tlkp_state;
> +------------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+--------------+------+-----+---------+----------------+
> | state_id | int(8) | | PRI | NULL | auto_increment |
> | state_abbr | char(2) | YES | | NULL | |
> | state_name | varchar(100) | YES | | NULL | |
> +------------+--------------+------+-----+---------+----------------+
> 3 rows in set (0.00 sec)
>
> The select with join:
> my $q = "SELECT * FROM dealer, tlkp_address, tlkp_state LEFT JOIN
>dealer_lead_method ON dealer.dealer_id = dealer_lead_method.dealer_id LEFT JOIN
>tlkp_dealer_lead_method ON dealer_lead_method.method_id =
>tlkp_dealer_lead_method.method_id WHERE dealer.dealer_id = $dealer_id AND
>dealer.address_id = tlkp_address.address_id AND tlkp_address.address_state =
>tlkp_state.state_id";
>
> And the updates that are run:
>
> UPDATE dealer_lead_method SET method_parameters = '[EMAIL PROTECTED]' WHERE
> dealer_id = 2 AND method_id = 1
>
> UPDATE dealer_lead_method SET method_parameters = '(414) 271-1814' WHERE
> dealer_id = 2 AND method_id = 2
>
> >How-To-Repeat:
> I have been unable to repeat this in the mysql monitor.
> >Fix:
> The updates execute normally if I SELECT * FROM dealer_lead_method WHERE
> dealer_id = 2 before updating any records for that dealer_id
>
> >Submitter-Id: <submitter ID>
> >Originator: nick edward purvis
> >Organization:
> CarSpot.com
> >MySQL support: none
> >Synopsis: UPDATE fails after select w/JOIN
> >Severity: non-critical
> >Priority: low
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-3.23.34a (Source distribution)
>
> >Environment:
>
> System: FreeBSD xtabi.carspot.com 4.2-RELEASE FreeBSD 4.2-RELEASE #3: Mon Apr 2
>11:10:40 CDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/XTABI i386
>
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
>/usr/bin/cc
> GCC: Using builtin specs.
> gcc version 2.95.2 19991024 (release)
> Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe
>-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
> LIBC:
> -r--r--r-- 1 root wheel 1169076 Nov 20 2000 /usr/lib/libc.a
> lrwxrwxrwx 1 root wheel 9 Mar 13 12:02 /usr/lib/libc.so -> libc.so.4
> -r--r--r-- 1 root wheel 559196 Nov 20 2000 /usr/lib/libc.so.4
> Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
>--without-debug --without-readline --without-bench --with-mit-threads=no
>--with-libwrap --with-low-memory --enable-assembler --with-berkeley-db
>--prefix=/usr/local i386--freebsd4.2
> Perl: This is perl, version 5.005_03 built for i386-freebsd
>
Hi!
Sorry but UPDATE's and SELECT's are totally unrelated in SQL. You do
not have to do SELECT prior to UPDATE, the only exception being (in
MySQL with InnoDB tables) SELECT ... FOR UPDATE which will only locks
rows involved, which is all.
If the UPDATE is not working then it could be a bug, but we need more
data to establish that. There was an update bug in the version you are
using, so I suggest you upgrade to our 3.23.38 binary for your OS.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
