Web lists-archives.org

RE: Question regarding Update ... LEFT JOIN

I hope it is locking both tables. Even if you aren't changing any fields in
the right-hand column, you don't want anyone changing it under you. I hope
that, if you aren't actually modifying the right-hand table, that MySQL uses
a read (SELECT) lock.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Jonathan Langevin [mailto:jlangevin@xxxxxxxxxxxxxxxxxxxx]
> Sent: Wednesday, January 03, 2007 4:13 PM
> To: mysql@xxxxxxxxxxxxxxx
> Subject: Question regarding Update ... LEFT JOIN
>
> I've used the syntax, UPDATE ... LEFT JOIN a few times in the
> past, and
> today I used it again for a new employer that I work for.
>
> Several of my associates were unaware that the UPDATE ... LEFT JOIN
> syntax is valid in MySQL. After I demonstrated that the query does
> indeed work fine on our MySQL 5.x server, one associate proposed the
> possibility that by performing an UPDATE ... LEFT JOIN, you could
> possibly be locking more than just the table being written to.
>
>
>
> I.E -
>
> UPDATE
>
>             Table1 t1
>
>             LEFT JOIN Table2 t2 USING (field1)
>
> SET
>
>             t1.field2 = '1'
>
> WHERE
>
>             t1.field2 = '0'
>
>
>
>
>
> Now, my actual query was more useful than the example I wrote
> above, but
> my question is, would Table2 be locked, even though it is only being
> read from, not written to?
>
>
>
> Any assistance on this matter would be of great assistance, and would
> determine whether I'll continue to use this update structure.
>
>
>
> Many thanks!
>
>
>
> RED VENTURES
>
> Jonathan Langevin
> PHP Developer (Home Services Corp)
> 14120 Ballantyne Corporate Place
> Suite 200
> Charlotte, NC  28277
> Tel: 704-971-4373
> Fax: 704-971-2303
> jlangevin@xxxxxxxxxxxxxxx <mailto:jlangevin@xxxxxxxxxxxxxxx>
>
>
>
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx