UPDATE using INNER JOIN

[csdude55]

I have two similar tables in MariaDB (essentially the same as MySQL) that I'm merging in to one.

Table A (soon to be eliminated) has 23,241 rows. The columns are:

id
subject
postdate (varchar 14; eg, 20220307202814)
username
comment
why

Table B (soon to eat table A) has 1.6 million rows. The columns are:

id
subject
postdate (timestamp; eg, 2022-03-07 20:28:14)
username
comment
filter_code

Everything is identical except that tableB.filter_code is all 0 right now, and I want to update it with the matching result from tableA.why.

My initial thought was to use:

UPDATE tableB SET filter_code=(
 SELECT why FROM tableA WHERE
  tableB.id = tableA.id AND
  tableB.username = tableA.username AND
  tableB.postdate = tableA.postdate)

But I'm concerned with this taking forever to run, since it has to work with 1.6 million rows 23,000 times :-O

I stumbled across using INNER JOIN for speed:

[sqlshack.com...]

But I rarely use INNER JOIN, so I'm hesitant to use it and mess something up.

Does this look right?

UPDATE tableB SET tableB.filter_code = tableA.why
 # a FROM here throws me off, but it's in the example above
 FROM tableB
 INNER JOIN tableA ON
  tableB.id = tableA.id AND
  tableB.username = tableA.username AND
  tableB.postdate = tableA.postdate

If so, do you think it's going to process significantly faster than the original?

[phranque]

i would make use of "EXPLAIN SELECT..." and "EXPLAIN UPDATE..." statements to see what's happening:
EXPLAIN [mariadb.com]

[csdude55]

Well duh (-_Q) LOL

First off, the INNER JOIN syntax was definitely off. A properly working code would be:

UPDATE tableB
 INNER JOIN tableA ON
 tableB.id = tableA.id AND
 tableB.username = tableA.username AND
 tableB.postdate = tableA.postdate
 SET
 tableB.filter_code = tableA.why

The first query (no INNER JOIN) showed that it would run on 1.6 million rows, while the second says it would run on 23,000. So it DOES appear to be much faster... assuming, of course, that it does what I'm wanting.

[edited by: phranque at 6:19 am (utc) on Mar 8, 2022]
[edit reason] formatting [/edit]