[watto]

'peterlaursen' wrote:

I think you finally figured out how to add files here! 🙂

Once you have the result set as in your image just change the dorpdown from 'Read Only' to the name of the table you want to update. Now the DATA GRID is writeable. Whenever you click on 'save* (the diskette icon) you will save. You will alos save whne the GRID-cursor moves to another row of the result set.

Please see image [attachment=1651:writeable.jpg]

Did this help?

Peter, Thanks for your reply

Did it help? Yes and No!

Yes, i can do the update manually.

But No in that this s something that i wish to do regularly and preferably not manually. I was hoping to discover if i could create some SQL to do the update?

I do have permissions to create 'views' if that would help?

Regards, Keith

[watto]

Hi Janani

As you can see i am new to this stuff (adding replies etc), sorry about that.

I have tried to attache a screen shot of how i identified the records i wish to update.

I wish to use a query to update the records.

Regards, Keith

[watto]

'watto' wrote:

Hi there

I am seeking assistance with an UPDATE i require to perform.

I am 'OK' with SQL but by no means really proficient with it.

I have identified the records that i wish to update through a query but i am unable to perform the update i wish.

Hopefully a screen shot will help (showing how i have identified the records i wish to update)

[img]http://C:UsersKeithDocumentsBusinessClientsASTSQLyogupdate posting.jpg[/img]

Image also attached.

In summary: I have 2 tables (account and packages).

Both tables have common key field of customer_id

Both table have a field named account_owner but i have a number of records in the packages table that are missing the appropriate account_owner detail.

What i am looking for is the appropriate SQL so that i can update the account_owner in the packages table to match the account_owner entry in the accounts table.

I do have UPDATE permissions but i am unable to create new tables in the database.

Help will be appreciated.

Keith

[watto]

'watto' wrote:

Hi there

I am seeking assistance with an UPDATE i require to perform.

I am 'OK' with SQL but by no means really proficient with it.

I have identified the records that i wish to update through a query but i am unable to perform the update i wish.

Hopefully a screen shot will help (showing how i have identified the records i wish to update)

[img]http://C:UsersKeithDocumentsBusinessClientsASTSQLyogupdate posting.jpg[/img]

Image also attached.

In summary: I have 2 tables (account and packages).

Both tables have common key field of customer_id

Both table have a field named account_owner but i have a number of records in the packages table that are missing the appropriate account_owner detail.

What i am looking for is the appropriate SQL so that i can update the account_owner in the packages table to match the account_owner entry in the accounts table.

I do have UPDATE permissions but i am unable to create new tables in the database.

Help will be appreciated.

Keith

[watto]

'peterlaursen' wrote:

You cannot connect to an Excel file directly from SQLyog Query Window. From here you will only connect to MySQL. SQLyog is built with the MySQL client/C-API that has no knowledge of JET or ADO or other such Microsoft database technologies . MySQL (server and client) is not designed for interaction with such Microsoft database technologies.

What you could try is to import the complete Excel sheet to a (temporary) MySQL table in your database. Next you could find a query moving what data you want from this temporary table to a 'permanent' table. To import from Excel directly to MySQL you'll need SQLyog ENTERPRISE or ULTIMATE versions – but with any version you can export to a CSV file from Excel and import using LOAD DATA syntax.

Alternatively (if you are a programmer) it is probably possible to write a .NET application doing this (but such application will use the MySQL Connector-.NET and not the C-API as SQLyog does). We cannot advise if you want to try this. We do not use .NET with MySQL – only C++, PHP, Python and LUA languages.

Thanks Peter, at least i can stop trying now!

I do have the enterprise edition and i have used the import process and then an update SQL command but i was hoping i could do a little quicker without importing to a temorary table.

Youe help is apprceicated.

I do have a local copy of the database and i havebeen thinking, can i do the update from within SQLyog to update the table from my local copy?

[watto]

'peterlaursen' wrote:

When you use the term 'field' do you mean 'column' or 'row'. I think you should provide a more detailed example (with a few rows of data before and after the process).

And also how do you plan to connect to Excel? From SQLyog import tool or 'MS Query' or what?

Hi Peter

I hope aliitle more information helps me to clarify waht i am looking for.

Here is my scenario:

My Database is called wbs_asstore

I have a table in my database called InfoStoreLog which has amny thousands of rows and approximately 60 columns

I downloaded my data from the table and have made adjustments to the values made in the 'Data Value' column on my downloaded copy which i have saved in an Excel file. I wish to update the 'Data Value' column entries on the table with the new values from my spreadsheet.

I am not very familiar with SQL and i really have no idea how to approach this.

Original Table

InfoStoreLog

ID Detail CostCode Data Amount

123 Test Record1 1548 15.82

567 Test Record20 1549 15.38

876 Test Record39 1581 15.57

359 Test Record58 1548 14.36

1357 Test Record77 1549 15.32

4689 Test Record96 2358 75.15

2743 Test Record115 2648 14.25

Excel Book: Path:C:Testing

Filename: DummyData.xls

SheetName: DataSheet

ID Detail CostCode DataAmount

123 Test Record1 1548 25.35

567 Test Record20 1549 24.69

876 Test Record39 1581 72.36

I was hoping i could do this direct from the SQLyog Query window using SQL similar to this:

Update wbs_asstore infostorelog Set infostorelog.DataAmount = DataSheet.DataAmount from

(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:TestingDummyData.xls',

'SELECT * FROM DataSheet$'))

WHERE infostorelog.ID = DataSheet.ID

Any assistance you can give will be appreciated.

[Author]

Posts