Can you perform field updates using sql query analyzer 1

[brendasql]

I am rather new to using Great Plains, but I have used sql 7 and sql 2000. I was wondering if I could perform a update query on RM00101 table to update the userdef1 column and the comment1 column? I have a spreadsheet that contains some key information on our customers and I would like to update these two columns with this information using the anaylzer instead of having users key in all this information. Any suggestions?

 

[winthropdc]

Below is a little test example I created. The Select statements show the fields before, during and after the test. I am using a transaction to make sure my changes are not permanent. If you want the changes permanent, either just use the update line by itself or change rollback transaction to commit transaction.

select USERDEF1, COMMENT1, * from RM00101

begin transaction

update RM00101 SET USERDEF1 = 'Test', COMMENT1 = 'Test'

select USERDEF1, COMMENT1, * from RM00101

rollback transaction

select USERDEF1, COMMENT1, * from RM00101


Hope this helps

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific

Micorosoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[brendasql]

Thanks so much for confirming this. I have been reading this forum trying to find out if anyone else was using sql to modify existing data within Great Plains. I am really happy to know that there is a forum here for Great Plains. I have been using this forum for years for SQL, VB6 and Access. I just recently changed jobs and will be working with Great Plains and could use the type of help that you get from others in this type of forum. Have a great day! And thanks again for replying to my question.

Brenda

 

[jymm]

Hey Brenda - I use SQL qry all the time to update the info for Dynamics - BE REALLY careful --- sometimes they store the info in multiple places, so often the trick is finding which is the info you want to change and which is not.

 

[VictoriaYudin]

I agree with jymm - be very careful. Usually I try to discourage updating transaction or summary type data directly in the db. However for the user defined and comment fields, you should not have any problems.

 

[brendasql]

Thanks for all your help. And yes looking over this database, "Being Careful" is putting it mildly.

 

[jymm]

BTW - there are some things that I do not have a problem with updating on the fly... other things I use Integration Manager. If you are not framiliar with that product, it will 'batch' all of your additions to the db and put them in the right tables - should be available through your partner.

I have used IM in the past to put out one record, then found the affected tables... then have written the needed triggers so that the database updates happen as we need them and not in batches... but that is just us ymmv