| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-08-26 : 04:37:25
|
| hello, how can I do the following.I have 2 tables with similar data in them, I need to merge the tables together.IF data exsits in table2 and table1 then update table1 with table2's data.If data exsits in table2 but not in table1 then insert the data into table1.the tables look lke :table1surname forname dob year other_infosmith chris 12/03/1990 2 jones steve 13/04/1988 8 Ptable2surname forname dob year other_infosmith chris 12/03/1990 2 RRjones steve 13/04/1988 8 Pdavies john 12/07/1991 1 Das you can see there is no primary key so I'll need to match on every field apart from other_infoon this example the first 2 rows will be updated and the 3rd row on table2 will be inserted into table1.thank you very much for help on this |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 05:09:46
|
| will this do?-- insert : this will get the data from table2 that are not in table1 and insert theminsert into table1 (surname, forname, dob, year, other_info)select surname forname dob year other_infofrom table2 t2 left join table1 t1 on (all columns you need)where (t1.surname is null) and (t1.forname is null) and (t1.dob is null) and (t1.year is null)-- update : this will get all the data that are in both tables and update t2.other_info with value from t1.other_infoupdate t2t2.other_info = t1.other_infofrom table2 t2 inner join table1 t1 on (all columns you need)Go with the flow & have fun! Else fight the flow :) |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-08-26 : 05:13:17
|
| when you say all the columns you need do you mean syntax like :(T2.surname = T1.surname) AND (T2.forename = T1.forename) AND (T2.dob = T1.dob) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 05:13:54
|
| yes, sorry but i was just too lazy to type it... :)Go with the flow & have fun! Else fight the flow :) |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-08-26 : 05:31:16
|
| thank you, its looking good, but when I run the insert some of the duplicates are being added, the data is the same, but there are nulls, whould that have any affect ?also the dob field is in text format. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-08-26 : 05:34:48
|
| sorry spirit, also the update query has a syntax error..I have added SET, but the error is missing query in operator 't2.field9 from blah blah' |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 05:44:49
|
| could be that, yeah... but if you have nulls in same columns in both tables try ((T2.forename = T1.forename) or (T2.forename is null and T1.forename is null))so what if the dob is text?Go with the flow & have fun! Else fight the flow :) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 05:46:20
|
| yeah i saw i forgot the set now that you metioned it. but what operator are you talking about? what error does it give you?Go with the flow & have fun! Else fight the flow :) |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-08-26 : 05:59:52
|
| nevermind mate, thanks for the help, my mistake !!I have modfied the insert query and it works perfectly.the update query is also fine !thank you again. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 06:01:30
|
| oh yeah the text field must be compared with likeGo with the flow & have fun! Else fight the flow :) |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-08-26 : 07:07:40
|
| what text field ? all the fields are datatype text, would I need to change the querys to T2.forename LIKE T1.forename etc for my data to be accurate ? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-26 : 07:49:01
|
| if it works, leave it :) the above post is my mistakeGo with the flow & have fun! Else fight the flow :) |
|
|
|
|
update data if it exists, insert if it doesn't