mysql update table from another table

General PHP Help
#1

I have two simple mysql tables with two columns each: ISBN and title.

The first table has some ISBN numbers where the titles are actually missing.

The second table has ISBN numbers where all the titles are filled in (none are missing).

I want to update the first table with the titles from the second table.

Example:

Table One:
0123456789 “The Bible”
1234567890 “”
2345678901 “Ten Commandments”

Table Two:
0000000000 “Hamlet”
1234567890 “Midsummers Dream”
2345678901 “Ten Commandments”

(because ISBN 1234567890 in table one is ‘empty’ I would like it to be populated with the data from table two, which in this example, is the title “Midsummers Dream”).

I have found this mySQL command works:

update Table1,Table2 set Table1.title=Table2.title where Table1.isbn=Table2.isbn

However, when I "google search’ the “correct” way to do this, most of the answers use an “inner join” syntax, like this:
update Table1 INNER JOIN Table2 on Table1.isbn=Table2.isbn set Table1.title=Table2.title where Table1.isbn=Table2.isbn;

The “INNER JOIN” style seems longer and more complicated. Is there some reason that my simpler version is bad?

#2

Why do you use two different tables at all…?

#3

I have two simple mysql tables with two columns each: ISBN and title.

The first table has some ISBN numbers where the titles are actually missing.

The second table has ISBN numbers where all the titles are filled in (none are missing).

I want to update the first table with the titles from the second table.

Example:

Table One:
0123456789 “The Bible”
1234567890 “”
2345678901 “Ten Commandments”

Table Two:
0000000000 “Hamlet”
1234567890 “Midsummers Dream”
2345678901 “Ten Commandments”

(because ISBN 1234567890 in table one is ‘empty’ I would like it to be populated with the data from table two, which in this example, is the title “Midsummers Dream”).

I have found this mySQL command works:

update Table1,Table2 set Table1.title=Table2.title where Table1.isbn=Table2.isbn

However, when I "google search’ the “correct” way to do this, most of the answers use an “inner join” syntax, like this:
update Table1 INNER JOIN Table2 on Table1.isbn=Table2.isbn set Table1.title=Table2.title where Table1.isbn=Table2.isbn;

The “INNER JOIN” style seems longer and more complicated. Is there some reason that my simpler version is bad?

#4

Hi,

Perhaps Jim’s question does deserve an answer…

Theoretically, given that ISBN are unique numbers why not to update all records in table 1 with the data from table 2.

However, the important question is whether both tables are part of one database or not.

#5

Hmm… no answer yet. Well, I guess while I’m waiting for someone that knows the answer, I might as well go ahead and answer JimL’s question (“why” do I have two tables?).

JimL, because one table is named “Table1” and the other is named “Table2.”

If I only had “Table1,” then I would have “one” table.

But because I have a Table1 and a Table2, then by definition, I have two tables.

Hopefully that helps you, JimL.

Meanwhile, I’ll again repeat the relevant portion of my question again, for the third time:

I use this syntax:
update Table1,Table2 set Table1.title=Table2.title where Table1.isbn=Table2.isbn

But according to my google searches for updating certain records on one table from another, I see examples of syntaxes using “inner join” (aliased tables, etc.).

Is there any advantage to using the more complex “inner join” style? Is there something bad about my brute-force syntax, or is it just fine:
update Table1,Table2 set Table1.title=Table2.title where Table1.isbn=Table2.isbn

Thank you.

#6
If I only had "Table1," then I would have "one" table.

But because I have a Table1 and a Table2, then by definition, I have two tables.

While I enjoy a smartass answer as much as possible, you are the one asking for help. The smartass response isn’t warranted.

WHY you need two tables doesn’t get answered when you have “Table1” and “Table2” that just shows lack of naming convention and poor data modeling for an issue.

The correct answer you are looking for is, you wouldn’t update two tables. Because, the same data should not be stored in multiple tables. It violated normalization and makes your data easily corruptible.

#7

Great way to make sure people won’t bother helping out.

#8

I will be more than happy to not help, ever.

#9

This is going nowhere.

The reason I asked was because of the XY problem. Read more:
http://xyproblem.info/

It’s a recurring theme that people ask a question where they need help to fix X (ie updating a table from another table). Other people may view the problem differently, or even recognize there are something else wrong in the code, db schema, architecture etc that would actually solve the problem if you fix that underlying problem instead.

So when I asked why you had two tables it wasn’t to be some smartass, it was to check if you had some reason for it. Or if we could just fix that underlying issue, which imo is that you have a less than optimal db schema.

I’m convinced the others in this thread had the same thought. Which is why when you instead of clarifying the issue just flat out make a fool out of yourself then you’re not making a good case for someone to bother spending time on your issue. Especially as we’re now 99,9% sure we’re at the last point in that XY problem page

After much interaction and wasted time, it finally becomes clear that the user really wants help with X, and that Y wasn't even a suitable solution for X.

I locked this thread as I have no hope of this thread ever becoming valuable to anyone.

#10

And I removed the superfluous comment and gave a lengthy cooling off period for Christy. If any other mods think that was too much, be my guest to change it.

Sponsor our Newsletter | Privacy Policy | Terms of Service