Inner Join Issues

Databases
1

SOURCE TABLE:

table name - datafrom

fieldname | datatype | len | default | not null?
titles | varchar | 250 | ‘’ | Y
sourcenames | varchar | 250 | ‘’ | Y

DESTINATION TABLE:

table name - datato

fieldname | datatype | len | default | not null?
titles | varchar | 250 | ‘’ | Y
names | varchar | 250 | ‘’ | Y

VALUES in datafrom

titles | sourcenames
Admin | Mike
Supervisor | Cory
User | Danny

VALUES in datato

titles | sourcenames
| Mike
| Cory
| Danny

I want to move “titles” data from “datafrom” into “titles” in “datato”, starting with the same row the user “Mike” is on and so on.

I tried:

UPDATE a
SET a.titles = b.titles
FROM datato a INNER JOIN datafrom b ON a.sourcenames = b.sourcenames

I received error:

Query : UPDATE a SET a.titles = b.titles FROM datato a INNER JOIN datafrom b ON a.sourcenames = b.sourcenames
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM datato a INNER JOIN datafrom b ON a.sourcenames = b.sourcenames’ at line 3
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

2

Guys, I moved the “threadid” column from the target table to the source table

Now I can do this:

UPDATE importedthreads
INNER
JOIN threads
ON threads.threadid = importedthreads.threadid
SET threads.title = importedthreads.threadshere

Now the thread titles will associate using the threadid

Still waiting on the query to finish…

3

That would be with an UPDATE statement since you don’t want to add new rows.

What you haven’t explained well enough is how you decide which title should be updated with an existing row in your current table and that is where you are having the problem.

4

i have a suggestion

export each table’s data into an excel sheet

copy/paste thread titles as per your wishes

then import the modified spreadsheet back into the target table, after clearing it out

5

Yay, it worked. Just need some association, I understand now.

Thanks you guys, very good support on this forum! =)

6

and how are these exported titles related to the threads you already have in your threads table?

you’ll probably have to go back to the originating database and export more than just the title column

7

where did they come from?

8

They are not related!

The DTS utility I am using to populate that thread table (SQLyog) doesn’t fill the “title” table, but it imports everything else!!

I just need the thousands of rows in the source table moved into that “title” column, without creating new rows

I wish I could explain this easier! Geeeeeez…this is the hardest DB work I have ever done!!

9

lol, at this point i am completely and utterly braindead on how i am going to move these thread titles into my production database

10

Query : CREATE TABLE importedthreads ( threadshere VARCHAR(250) ) ENGINE=MyISAM
Error Code : 1050
Table ‘importedthreads’ already exists
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

11

that’s it :slight_smile:

except you wouldn’t want to actually run that, because you already have that table – what’s important is the actual table and columns, the structure of the table

try it again with a table with more columns and at least one index

and of course this is way better than a screenshot because now i can copy the CREATE TABLE statement and run it on my system – with screenshots i would have to retype everything, which i prolly wouldn’t bother to do

help us help you, eh

:slight_smile:

12

nope :slight_smile:

instead of another screenshot, grab the actual CREATE TABLE statement and use copy/paste to put the generated text into your post

something like this –

CREATE TABLE importedthreads
( threadshere VARCHAR(250) 
) ENGINE=MyISAM ;

try it on a different table, one that has more columns and at least one index

13

that’s prolly a good idea

14

oh, and screenshots is a less than optimal way of conveying table structure

i don’t recognize the query tool you’re using there, but i’m sure there’s gotta be an sql window where you can run queries to test them

so just run this query and see what it produces:

SHOW CREATE TABLE importedthreads
15

Darn it!

That is what I was afraid of…

In the example you gave me earlier, it updated the rows, not added, but the “ON datato.id = datafrom.id” added a common value between the two, perhaps I need to find one, like import the threadid’s into that source table and then run the query…

16

i’m sorry, i’m even more confused now

it’s beginning to look like rather than doing an update, you want instead to import all the rows of the importedthreads table into the thread table

but then, looking at your thread table, it looks like the existing rows have no titles

so maybe you are looking to do an update after all

well, i’m sorry, the way things stand, there is no way to match rows from the importedthreads table with rows in the thread table

you can import them, but they’ll create new rows in the threads table

17

Let me post screenshots:

SOURCE TABLE:

DESTINATION TABLE:

See, only one column in the source table, I need to bring all those rows into the destination table, to the “title” column.

18

Yeah?

19

my point about there being nothing to update still stands

but you’re contradicting yourself

first you say

the source column has “title”
but then you say
there is no “id” column in the source table, only the “price” column

i’m sorry, i cannot work with such ambiguity

any chance you could do a SHOW CREATE TABLE for both tables?

20

Well, the source column has “title” and the target column in another table has “title” and some other columns, I just see that “ON datato.id = datafrom.id” statement but I was worried about running that when my actual production tables, there is no “id” column in the source table, only the “price” column if taking the column names from your example…