I have two tables with identical structure (one is an old backup), and I'm trying to update a column in one table with content from another. Like so:

UPDATE t1, t2
SET t1.comment = t2.comment
WHERE t1.username = t2.username
AND t1.postdate = t2.postdate


If I understood the manual correctly, this SHOULD work, but the descriptionin the manual was a little confusing to me so I'm not 100% sure. The logicis, update t1.comment with what's in t2.comment, if t1.username matches t2..username and t1.postdate matches t2.postdate.

I was particularly confused about "UPDATE t1, t2". Why do I enter both tables, if I'm only reading t2 and updating t1?

I tried running the query, but after about 30 minutes I had to stop it. Table t2 has about 285,000 rows in it, though, so I'm not sure if it should really take that long, or if I got caught in a loop or something.

Am I correct in my understanding of this, or do you guys see a glaring error that I'm overlooking?

TIA,

Jason

Jason C wrote:
t1.x=t2.x where...etc etc...


It will probably take hours especially with no indices on username and
postdate..I've done similar here..


Nope. Looks good to me. But a guru I ain't.

You might try a limit clause to see if its working first on a smaller
data set.

On 11/1/2011 5:13 AM, Jason C wrote:
It should work fine, although you really only need "UPDATE t1 SET ..."
(you're not actually updating t2). Also, if it didn't work, I would
have expected an error long before 30 minutes is up.

30 minutes does seem long, but a lot depends on your tables. With no
indicies, MySQL will have to do a full table scan for each row found -
which is going to be very time consuming. You should get a lot better
performance by creating an index on (username, postdate) in both tables.
You can always drop the indicies later.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:

Is there no primary key on those tables that you could use for the
JOIN? Is (username, postdate) supposed to be UNIQUE?

Yep. That's what it will do.

You have to tell MySQL which tables to use for the JOIN. Which tables
to update follows from the SET clause. Multi-table UPDATE is not in
the SQL standard, so MySQL was free to define a syntax.

If you look at multi table DELETE syntax, then it's more complicated.
In the absence of a SET clause you must specify a list of tables to
delete from and a second list of tables to be joined.

Actually a JOIN like this is executed in a loop. Probably there was
just no good index. You can run EXPLAIN on the equivalent SELECT:

EXPLAIN SELECT t1.*, t2.comment FROM t1, t2
WHERE t1.username = t2.username
AND t1.postdate = t2.postdate

This would tell you in which order the JOIN is done. The second table
then should have an index on (username, postdate).


XL

El 01/11/2011 10:13, Jason C escribió/wrote:
You obviously need to tell MySQL that it needs to read t2. There are
actually several variations of the SELECT+UPDATE syntax. In the one you
use, you specify all table names in the UPDATE clause. There's another
variation that I particularly find more intuitive:

-- Don't run blindly: I'm not sure it does the same as yours
UPDATE t1
INNER JOIN t2 ON t1.username=t2.username AND t1.postdate=t2.postdate
SET t1.comment=t2.comment;



With the second syntax, you can test the select part quite easily:

SELECT *
INNER JOIN t2 ON t1.username=t2.username AND t1.postdate=t2.postdate
SET t1.comment=t2.comment;

Whatever, it all depends on your row count and indexes.



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Tuesday, November 1, 2011 5:13:25 AM UTC-4, Jason C wrote:

Thanks for all of the help, guys. You were all correct, the code IS solid. I created an index (no UNIQUE, since I had some errors in the data that created a few thousand rows with "000" in the postdate, making none of them unique), but then I also found that phpMyAdmin still wouldn't run it. I guessthat PMA required more memory than was allotted, because running it via SSH had it finished in 6 seconds!

OK, let's get tricky with it now...

In these tables, postdate is varchar(14). How can I modify the code to update if the username matches, and the first 13 characters match?

I know that you can't use a REGEX with UPDATE (at least, that's my understanding), and I'm sure that I can do it with a PHP script. But can it be done with a direct query?

El 10/11/2011 6:33, Jason C escribió/wrote:
There's no particular rule about REGEX and UPDATE :-?

UPDATE t1, t2
SET t1.comment = t2.comment
WHERE SUBSTRING(t1.username, 1, 13) = SUBSTRING(t2.username, 1, 13)
AND t1.postdate = t2.postdate

.... but you don't need a regex to extract a substring.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--