Create Table From Left Join Select Query

[mtht]

Hi, I'm new to MySQL and can't seem to find the answer to a, i hope, rather simple question. Select * from t1left join t2on t1.field = t2.field Can i get this result into a new table?Or can i update t1? Any help on how to get about this issue would be very much appreciated!!!

[birbal]

did you try runing the query?join does not allow wild card (*)

Can i get this result into a new table?

you can insert the result in another table but you have to create that table first. and all column should match the data type also.

can i update t1?

you can use the joined tables as you did with other tables. join just show the tables depending on a condition. t1 and t2 table can be updatded.

[mtht]

Thanks for your reply!! Actually it runs fine with * but maybe that's because i'm in Navicat. So i'd alter the first table: ALTER TABLE t1ADD COLUMN t1.field1 VARCHAR (255), t1.field2 VARCHAR (255) right? and then i'd like to put t2.fieldB into t1.fieldB and t2.fieldC into t1.fieldC where t1.fieldA = t2.fieldA But how should i write this down Thank you for your time to help a noob!

[mtht]

So add 2 columns to t1 like the ones in t2 and then put them into t1 like: ALTER TABLE t1ADD COLUMN t1.fieldB VARCHAR (255), t1.fieldC VARCHAR (255)UPDATE t1SET t1.fieldB = t2.fieldB, t1.fieldC = t2.fieldCWHERE t1.fieldA = t2.fieldA ?

[mikev1976]

Are the two tables tied together via keys or are they 2 unrelated tables? Can the values in t1.fieldA be repeated in the t1 table or are they unique? If they are unique ids then something like this should work: update t1 set t1.B=t2.B,t1.C=t2.Cfrom dbo.Test_Table_1 as t1 join dbo.Test_Table_2 as t2 on t1.A=t2.A where t1.A=t2.A Can you post some of the values from each table so we have a better idea? I am kind of new also and am trying to learn as well...

[justsomeguy]

If you create the destination table first, you should be able to fill it with a select query. INSERT INTO table Select * from t1left join t2on t1.field = t2.field You just have to make sure that the destination table has the same columns that the select query will produce, or appropriate defaults.

[mikev1976]

How about if he wanted to update table1 instead of create another table?

[justsomeguy]

Updates work fine with joins.

[mikev1976]

Yea I made a couple of tables and was trying to do the task at hand but questions came up concerning duplicate values, keys, etc. The problem I encountered with just updating table1 was that if table1 column1 had duplicate values and table2 column1 had duplicate values that were the same as table1 column1 then only the first entry in table1 got updated. Table_1 A B C Allen Crap 032 Allen Johnson 045 Allen OMG 373 Allen Smack 026 John Smith 076 Steve Moses 840 Table_2 A B C Shane NULL NULL Allen Reno 789 Allen Williams 456 Shane Carver 123 Allen Craig 045 Allen Sanchez 032 update t1 set t1.B=t2.B,t1.C=t2.Cfrom dbo.Test_Table_1 as t1 join dbo.Test_Table_2 as t2 on t1.A=t2.A where( t1.A=t2.A and t1.B!=t2.B and t1.c!=t2.c)Table_1 A B C Allen Reno 789 Allen Reno 789 Allen Reno 789 Allen Reno 789 John Smith 076 Steve Moses 840

[mikev1976]

I wasn't sure if there were keys or not so I added another column as a unique identifier. Then could update with no problem. update t1set t1.b=t2.b,t1.C=t2.Cfrom Test_Table_1 as t1 join Test_Table_2 as t2 on t1.D=t2.D

[mtht]

Thanks for the replies!! first: @ Mikev: All fields are varchar and the tables are unrelated except off course for a random simularity in field a of both tables. also there are primary keys but both tables start at 1 so they aren't unique. Would a unique id/ key help?And as for your sollution:

update t1 set t1.B=t2.B,t1.C=t2.Cfrom dbo.Test_Table_1 as t1 join dbo.Test_Table_2 as t2 on t1.A=t2.A where t1.A=t2.A

The thing is: the table contains like 22 fields so it would be tedious (not to say almost nerve wrecking with long field names)So i tried:

INSERT INTO table Select * from t1left join t2on t1.field = t2.field

But when i run: INSERT INTO c SELECT * FROM a LEFT JOIN bON a.1 = b.1; i get: [Err] 1136 - Column count doesn't match value count at row 1 @justsomeguy, Any ideas what i'm doing wrong?

[mtht]

@Mikev, Are you creating 2 simulair tables? wouldn't that take twice as long? (Just wondering) Also the tables would look more like: t1(id, name, street, whatever, +10 fields)t2(id, name, occupation, some other thing, + 21 fields) so: SELECT* FROM t1 LEFT JOIN t2ON t1.name = t2.name results in: result view (id, name, street, whatever, id name, occupation, some other thing) (and the other 25 fields) And this i would like to put in a new table or update t1 or t2 for that matter. That's why i liked: INSERT INTO t3 SELECT * FROM t1 LEFT JOIN t2ON t1.name = t2.name; But it gives me:[Err] 1136 - Column count doesn't match value count at row 1 At this point i'm exporting the result to a txt file to import it again.pretty boring, not to say incredibly slow because t1 has 32000 and t2 600.000 lines.the select query takes a day and a half on a dual core pentium D processor. (Yawn...)

[mikev1976]

If your tables have a different number of columns you can't just insert info via select *. The column data has to match up in all the tables. So you can update what you want specifically by certain column data and under certain conditions. At least that is my understanding. Update this table column where this table column blah blah blah blah... If table one has 10 columns, and table 2 has 50 columns, you can't just insert all of table 2 columns into table one because they info doesn't match up....I will make tables w/ different columns and see if I can do the same thing....

[mtht]

Thanks for your time Mikev, i really apreciate it!And if your right, i don't get it. "If i can get it into my result view, i should be able to have it as a new table..." right?...

[mikev1976]

I added some more columns to both tables. The first table with 8 columns. The 2nd table with 15 rows. I was able to insert the information from table1 and table2 where table1.FirstName=table2.FirstName2. But in order to do so, the 3rd table had to have the same number of columns and the same names as table 1 and 2 combined. So table 3 had to have all the columns from table 1 as well as all of the columns from table 2. insert into DumpData select * from Test_Table_1 as t1 left join Test_Table_2 as t2on t1.FirstName=t2.FirstName2 I am still experimenting and was just curious as how to do this. This works but there may be a better way. I think you should be able to update your table also, you would just need the columns there to be able to accept the data.