This is a question about the best approach to tackle my present problem

Databases
1

Hello my good friends,

I am quite confused about the right approach to handle this problem of mine with mysql

My knowledge is very limited and seek opinion. I need to come up with something like the following pseudo code dipicts.

UPDATE rmc_raw_data
INNER
JOIN server_log_data
ON rmc_raw_data.rmc_time = server_log_data.server_rmc_time
AND rmc_raw_data.rmc_date = server_log_data.server_rmc_date
AND rmc_raw_data.latitude = server_log_data.server_latitude
AND rmc_raw_data.longitude = server_log_data.server_longitude
SET data_source = “Both”
ELSE SET data_source =“Mobile”

and again reverse the comparison but this time:
SET data_source =“Both”
ELSE SET data_source=“server”.

Only two table are involved but i dont seem to handle the comparisons well or even the right approach. Is it permiseable to use the AND operator the way I have used it above in MYsql?

The above scenarioa is my predicament and I need some one to tell if I can achieve my desired result with a query or procedure or php code but must importantly how to approach such. I am a very confused new user at this very point and someone should please give me some clue. I would appreciate.
Please if you dont understand I would explain more.

Please dont be alarmed by this question. I am learning gradually. many thanks. r937, kalon, Guido etc etc please assist you guys are great.

Paul

2

If you’re going to merge the two tables in the end, you might as well merge them immediately and add that datasource column only to the new (merged) table.

3

Thanks Guido. So now that i have them merged what solution would you please suggest.
Please share your solution with me am quite helpless at this point.

4

that was my suggestion – INSERT with ON DUPLICATE KEY UPDATE option

5

I have just researched the duplicate key update. I do not know how it to my present problem please hope am not asking too much on this matter. I need a clue at least then i can understand more

6 
INSERT
  INTO server_log_data
     ( server_rmc_time 
     , server_rmc_date 
     , server_latitude 
     , server_longitude
     , other_columns
     , datasource ) 
SELECT rmc_time      
     , rmc_date     
     , latitude     
     , longitude
     , other_columns
     , 'mobile' 
  FROM rmc_raw_data
ON DUPLICATE KEY UPDATE data_source = 'both'

don’t forget you have to set up the 4-part index –

ALTER TABLE server_log_data
ADD INDEX 
( server_rmc_time 
, server_rmc_date 
, server_latitude 
, server_longitude )
7

hi Guido, r937.com here is a sample of the structure of the table and the actual data. I hope this makes sense in any way. many thanks

8

thanks for the suggestion. but before i explore it. there is no crime having datasource field in both tables. Could this make the solution easier to find if i have it in both table and if so what will it look like then?

9

okay, you still don’t seem to see the problem

suppose you have this for table A –


date       time     long   lat  datasource
2010-09-09 09:37   -45.2  76.3
2010-09-10 09:37    53.2  13.1
2010-09-11 09:37    11.4  44.8

and this for table B –


date       time     long   lat  
2010-09-09 09:37   -45.2  76.3
2010-09-11 09:37    11.4  44.8
2010-09-12 09:37    33.9  24.9

looking at just the dates, they are september 9, 10, 11, and 12 – all the other columns will match

so table A has 9, 10, and 11, while table B has 9, 11, and 12

obviously, we will update the datasource column on table A to say “both” for 9 and 11, and we will update the datasource column on table A to say “server” for 10

but how do you update the datasource column on table A to say “mobile” for september 12?

that’s your current problem

10

oh, i understand fine

the problem is that the datasource column exists in only one table

it almost sounds like you want to insert the rows of B into A, and update the datasource column afterwards

11

Thanks for the question. a good one.

If in table B(mobile) there are data entries for columns lat, long, time, date whose values do not match the values of lat, long, time, date for columns in A(server), then the datasource column must have “Mobile” for those particular row entries that dont exist in A. In this case you are using B as the pivot table. Where they match the column is set to “both”

In the next case A would be used as the pivot table and the same would hold. This is what i intend to achieve. I dont know if you understand I can be more clearer with sample data. Thanks my friend.

12

what would i suggest?

how about doing an INSERT into table A from table B, with the ON DUPLICATE KEY option so that when a matching row is found, it updates the datasourceto “both”, otherwise it inserts a row with “mobile”

but this would only work if you allowed rows from table B to get into table A

i asked you this earlier, but you did not pursue it…

13

First thanks for the concern.

I have 2 tables having the same structure and holding the same information but different field names. The difference between them is the source of the data they hold and of which am interested in the datasource.

I want to be able to know if the 4 key fields in both tables have the same values. If this is true I would insert “both” into a field called “datasource” in one of the table. For example:

Given table A (datasource is server) Table B (datasource is mobile)
Adate Bdate
Atime Btime
Alongitude Blongitude
Alatitude Blatitude

two data sources where used to obtain the data for A and B and for A, one datasource gave data for A and B which was separated.

Now I have an extra field in A called datasource. In this field, i would insert “both” if above 4 fields in A and B have same values in date, longitude lat, time (which means both data sources gave same entries) ELSE I would insert " server" which means the datasource is server. Second, I reverse the comparison and this time, Insert into the same column “both” if the values of the 4 key fields match or ELSE INSERT “Mobile” which means the datasource for the table which is the basis of the comparison is the mobile.

So it is like taking TableA (source of data is “server”) as main and comparing with B to insert “Both” or “server”

and then taking TableB(source of data is mobile) as main and comparing with A to insert “both” or “mobile”.

So at the end I have the extra field which would have either “both”, “server” or “mobile” based on the comparison and the reverse comparison of the values of the 4key fields of tableA and Table B

This is what I am trying to do and i hope you understand me if not please ask me where you are not clear and i would be ready to explain. This problem at the main time has stoped me from progressing at all. Please thanks for you assistance ones again.

Regards
Paul

14

the ANDs are fine… the ELSE is wrong

you forgot to mention what you are trying to do

the ELSE (which is wrong) indicates some kind of condition, but you need to tell us what it is

15

Paul, the point is you said:

Now I have an extra field in A called datasource.

I’m going to make a wild guess: you have the same column in table B too?

16

thanks for your understand. Understand is the begining of a possible solution.

17

It is like a pseudocode of this nature.

if date_server =date_mobile then
if time_server=time_mobile then
if lat_server=lat_mobile
if long_server=long_mobile then
set datasource = “both”
else “server”
// next I repeat loop this time with mobile in reverse order with table A as pivot or as the case maybe.
else “moble”

Please pardon my limited knowledge. the above pseudo is what i think but implementation is like hiitting the rocks. This is the last stage of analysing my data and i simply cant move on.

18

okay, go back to post #10 and explain how the unmatched “mobile” rows are to be treated

19

those two tables hold similar information. the importance of having them is because the server was used to get the information of one and the mobile phone is used to get the information othe other. and now when the magicupdate is done would do post analysis to see the number of servers that transmitted, howmmany both transmitted and how many the mobile which will lead me to make an inference.

those tables would be kept for more dat that i would still collect and do same process. I hope this make sense to you.

20

no Guido