Merge Data Question

[gaia]

SQLyog seems to be the most fully featured tool out of the ones I compared. There is one thing I couldn’t do with it (neither with other tools)

 

Table A has several columns, including ‘code’ and ‘date’. ‘code’ is PK

 

a-update.csv has two colums, ‘code’ and ‘date’. There are no dups in ‘code’.

 

I want to update table A with the ‘date’ of a-update.csv where ‘code’ matches.

 

Is it possible to do this without having to copy the entire table to CSV, merge in excel, upload?

 

Thanks!

[peterlaursen]

Please reply to one question: can it happen that the .csv has a row (as identified by `code`) that does not exist in the database? If fhis is possible, what should then happen? Should such rows be INSERTED to the database or should they be ignored? 

[gaia]

Please reply to one question: can it happen that the .csv has a row (as identified by `code`) that does not exist in the database? If fhis is possible, what should then happen? Should such rows be INSERTED to the database or should they be ignored? 

 

All codes in the csv exist in the DB. As a precaution, they should be ignored.

 

Thank you for noting that I failed to inform this.

[Kartik Grover]

Hi 

 

The update that you want can be done by using the Import External data Tool in SQLyog. You can go to the Powertools>Import External data tool.

 

->This will open a window to start a new job.. Then select the type of data source (in your case it is csv) and specify the location of the a-update.csv. Click next and select the destination database. 

 

->Then select the copy table(s) from data source. In the window that follows you will see different mapping options. Selecting the a-update.csv, and the destination as ‘Table A’.

 

-> Clicking on the Map tab will give the columns names i.e ‘code’ and ‘date’ In both the source and destination tables. Since you only want to update the ‘date’ column you must check the ‘date’ in source.

 

-> Now to give the required ‘where’ query click on the ‘Where’ tab and enter the query (in your case it would be= [`a-update`.`code`=`Table A`.`code`])

 

-> You can specify what to ignore and what to add while the import from the Advanced tab.

 

For more info on how to import you can refer..>> http://sqlyogkb.webyog.com/category/270-import-external-data

 

Regards

[gaia]

Almost there.

 

Since MySQL expects DATE and DATETIME literal values to be single-quoted as strings like ‘2001-01-01 00:00:00’, does SQLyog handle that or does the data source need to have the single quotes around the date field?

 

I can’t get the where clause to parse. I assume that the name can’t be the file name, but it has to be the data source name SQLyog creates (and it says do not change the datasource name) `sqlyog_temp_1425989092`. Is this correct?

 

There is no = or [] in the string I need to enter. its just `sqlyog_temp_1425989092`.`code`=`table_a`.`code`. (The table name has no spaces or uppercase) Is this correct?

 

I tried a few options but it didnt parse.

 

thanks!

[Author]

Posts