RE: Question regarding Update ... LEFT JOIN
That would be fine with me, but then conversely it means multiple tables
would be locked by one query, which is bad in our environment (high
transaction)
-----Original Message-----
From: Jerry Schwartz [mailto:jschwartz@xxxxxxxxxxxxxxxx]
Sent: Wednesday, January 03, 2007 4:24 PM
To: Jonathan Langevin; mysql@xxxxxxxxxxxxxxx
Subject: 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