Nested Update

[PaulNelson]

Hi all,

I am having a bit of a problem with implementing a nested statement that will update based on conditions. I know the logic behind it, but do not know how to implement it with nested queries. Please see below the pseudo code I've written along with an example of what I want to do.

IN TABLE #NAME# IF COLUMN A IS NOT EQUAL TO COLUMN B SET COLUMN C AS 'TRUE'
IF COLUMN D IS NOT EQUAL TO COLUMN E SET COLUMN F AS 'TRUE' ELSE SET AS NULL

What I want to do is something along these things but in one query instead of multiple

UPDATE tmpDifferences SET Diff1 = '*' WHERE bTerm <> aTerm
UPDATE tmpDifferences SET Diff2 = '*' WHERE bDefinition <> aDefinition

But I want to do it in a nested fashion along the lines of

UPDATE tmpDifferences ((SET Diff1='*' WHERE bTerm <> aTerm) SET Diff2='*' WHERE bDefinition <> aDefinition)

I don't know if this makes sense to anyone or not but if you need further clarification please let me know. It's frustrating when you know the logic but can't write the code to satisfy it.

Thanks in advance guys! :)

[tesuji]

Both updates require different where-clauses. Therefore you can't do these updates within one update-statement.

May I ask you why you would like to execute both updates within one statement?

-- tesu

[PaulNelson]

Thanks for your quick response.

I am designing a process to compare data that is dumped from a database (SAP) and need to validate the 'before' and 'after' data to verify that nothing has changed. So I guess I need to run 10 different queries to compare 20 columns (10 sets)? It seems that this should be able to be done and if not seems to be a flaw in SQL design.

Is there a work around I could use so that I don't have to execute multiple statements?

[tesuji]

Do you select directly from R/3 tables or make use of sap transport system? If selecting directly, you may explicitely lock the concerning R/3 tables to be sure that nobody makes changes before finishing your copy-transaction.

On sql server I would write a transact stored procedure. The effort to spend for 10 or 20 sql selects and further for 10 to 20 updates isn't that important when running in a procedure.

Idea:
Instead of comparing each value individually, sometimes it's useful to work with checksums. While fetching the data for copying them you compute a before-checksum. After finishing copy-transaction successfully you compute after-checksum. If they differ, you may rerun copy-transaction.

-- tesu

[PaulNelson]

What I'm actually doing is an export of data in excel and then porting it into Access 2007 where I am writing SQL scripts to analyze the data. Checksums won't work as data is bound to change and we want to be able to verify that data has not changed after transports by looking at line item level detail. This is why I need to have a field that displays whether the before and the after has changed, this way we can filter our results to see if it is an acceptable change (such as time generated) or if it is unnacceptable (corrupted data).

I guess I am destined to write multiple queries to populate the columns. Thanks for your time and effort.

-Paul