THANKS! Need a very SImple Update Script

SQLHELPasap

Hi All,
I am not a SQL expert but am very aware of the dangers associated with any update script for NAV. I just need to change ONE field value on ONE table (ONE line) from null to the value of "4". (I expect this to be a very benign change and accept the fact that it may not work but want to try it.) I am able to query the table and see the field without any problem. We are not able to update the table using Object Designer as we get the message that the update is not allowed as there are "ledger entries" associated with the table. This is a custom table but the field is a simple value and the ledger entries all show the value properly.

We are doing this in a back up db and want to move ahead. This is an older Classic client version of NAV (5.0) and we no longer have support on any front.

As an example: table name in NAV is XX$Task Order Detail (this is a "card" in NAV) and the field is, for example Order "Type" with the numeric values on the table of 1 through 6. Right now the "Type" is null and needs to be "2".

If someone could help me with how to write an update script I would be most grateful.

bbrown

Since NAV doesn't allow NULL values, how could you have this situation?

bbrown

Also, if you are not familiar with T-SQL, then I'd suggest you write a NAV process-only report to handle this situation.

Luc_VanDyck

bbrown wrote:

Also, if you are not familiar with T-SQL, then I'd suggest you write a NAV process-only report to handle this situation.

He will need a developers license though to add permissions to the Entry table, in order to modify records.

bbrown

He doesn't say this is an Entry table. In fact, he states that it's a custom table. The ledger records are related. So most likely he does not need a developer's license.

kapamarou

If you search google you'll see plenty of examples. Isn't it easier to do it through SQL Management studio?

SQLHELPasap

I am using SQL Server Management Studio but do not have the background to write the exact update statement. I was just looking for a simple sort of "shell" if you will. I have the necessary permissions. This does not need to be over complicated, my statement is just erroring out as I am not famiiar with using this tool and am obviously missing something.... OnTuesday, I'll include the actual statement I am trying to use and the error I am getting...it is a syntax error so I have something out of order or improperly phrased..

Thanks all, have a happy and safe NEW YEAR!

davmac1

In Management Studio, you can edit a field unless it is non-editable. If it is non-editable, you can change the field definition in NAV then go back to Management Studio and edit it.
For one field in one row - this is a lot simpler way if you don't know SQL.

SQLHELPasap

The field is marked as editable in Object Designer. That is the reason I am in this quandry..

bbrown

The "editable" settiings in Object Designer has nothing to do with whether or not you can edit data directly from SQL. Niether do a user's permissions defined in NAV.

SQLHELPasap

Issue is resolved..thanks,I figured out how to write the update script myself and all is well..processing continues with no errors. Woot! \:D/