SQL Update fields ?

[SBSP]

Hi I have a bit of an issue with our ERP system.

In Accounts payable there is a screen that show's
[ITEMNUMBER] [DESC] [ACCOUNTNUMBER] [ACCOUNT DESCRIPTION]

The problem is the Item description is showing the same string as in the account description field.
To me its not supposed to be like this ,and a dev company is asking 13K to do custom dev.

They quoted for 3 days. That's not on it wont take 3 days to do this.
And they will be creating a process that runs and updates the fields outside the system not even modifying the system to update the correct string
from Purchase orders.

So I created a view that links the PO Header table with the PO Detail table (on a unique sequence number) and links to the AP Header and AP detail table
also on a unique sequence number)

And I'm linking the PO Header.PONBR field to AP.PONUMBER table.

So i now have a view that shows [PONUMBER] [ITEM] [ITEMDESC from AP detail] and REALITEMDESC(FROM PO detail table)

That's is what i have done sofar and it works fine. The view also is writing back to the tables when i change
the data in the fields in the view.

I now need to take the view as is and run a SQL script that will put ITEMDESC = REALITEMNUMBER through out the whole
view i dont need any where statements.

To make things clear the database is called.

BACKUP and the view is called AP_ITEM_DESC
and the view has 4 fields [PONUMBER] [ITEM] [ITEMDESC] and [POITEMDESC] Obviously these are not the real table names but it should do.


Is it possible to select from and into the same table at the same time ?

[RuadRauFlessa]

Yes.

Code: Select all

update table AP_ITEM_DESC set ITEMDESC = REALITEMNUMBER

There you go. Simple update should do the trick. I would not try this on a live system though. could be a tad risky. For long term results you may want to look at creating a trigger to update the value if you won't be changing the front end to do the work.

By the way, what ERP system is it?

[SBSP]

Shot Bru!

thanks, I didnt know you can use a trigger.

I assume the trigger will watch those tables and as they come by it will update them ?
Sound like it will have a bit of an Impact on the system performance wise.

If i do this on the system it will run from line 1 to the very last line every time.

I will do this once so it updates all the history.

If i want to run this from a specific line, I take it i will need a unique sequence number.
Can i do this on the PO number ? PO numbers a insterted as PO0000000 to PO9999999
will i have to substring the the 'PO' part out and convert the rest to a number ?
or will it work if i just go E.G 'Where [PONBR] > 'PO1274656' ?

Its ACCPAC , the front end also supports VBA, So i could find some VB6 code that will exec the SQL statment on the database
by a click of the button.

Or i could schedule a stored proc, but then it will only update in intervals. Knowing users they will ask me to update it for them cus its urgent.

[RuadRauFlessa]

Nah create a trigger that will only update the specific record if it gets created or if the specific field changes. Won't have too much of an impact if you do it correctly.

Where [PONBR] > 'PO1274656' will work just the same as a number provided that the length of your varchar stays the same. But there is no need for this as with a trigger you can alter the line before it is even written to the table. It is actualy more efficient than doing a stored proc. You will need to get the others up to date before you make this active though. Else you will sit with inconsistent data.

Used to work on ACCPAC and ACCPAC CRM for my previous employer. Even did my dev and admin courses on it Very powerful, very flexible but also very unstable if Acctech had their hands on it.

[SBSP]

Thanks allot.

I will be trying the trigger thing, hope i dont stuff anything up!

[RuadRauFlessa]

Always good to do it on a backup of the db first. So make a backup and load that backup into a different db. Create the query and test it by inserting new lines and by updating them. Check the results and if they are not 100% what you expect either drop it or revise what you did and try again. Rinse and repeat. Remember safety first. You can never bee too cautious on a live system.

[SBSP]

Thanks.

I'm terrified when it comes to something like this.
I have a backup company loaded but because i work with the live company database name (Crystal reports and all that)

I have managed to execute a querry on the live database before thinking i'm on the backup.

But for this i will use my test environment

BTW i dont know how to create a trigger, i dont see anything about triggers in the SQL enterprise manager. Will use google.

[DeathStrike]

3 days of work??? OMG... now that company wants to rip you guys off. glad u got your code tho. good luck and test on a separate system first mayb copy the DB first and then test. else if you mess it up u could get into big trouble.

[RuadRauFlessa]

Same as creating a stored proc except use the below syntax

Code: Select all

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ENCRYPTION ] | [ EXECUTE AS CALLER | SELF | 'user_name' ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ]}

Then take a read over here

[SBSP]

Hi Thanks.

Before i do the trigger i would like to get a stored proc to work atleast.

Code: Select all

 
CREATE   procedure z_AP_ITEM_Update as

UPDATE TABLE APDESCINV set TEXTDESC = ITEMDESC

GO

I get an error saying incorrect syntax near the Keyword table

[RuadRauFlessa]

remove the keyword see what happens. Don't remeber the exact syntax now.

http://www.comptechdoc.org/independent/ ... pdate.html
http://msdn.microsoft.com/en-us/library ... S.80).aspx

[SBSP]

LOL Okay

Thanks found the problem.
SQL should know what i mean.

[RuadRauFlessa]

Yeah but you aint working with an AI. It is Microsoft we are talking about here. Not that any other db is any better.

[Hex_Rated]

I love triggers, they've saved my a$$ with merge replication. *spits*

[RuadRauFlessa]

I love triggers, they've saved my a$$ with merge replication. *spits*

+1

Had the need for them more than once.