Update data in specific tables in specific columns in database

[RRinTetons]

The Task: Update all accounting general ledger segments in a database. There are 4 account segments. Tables that contain gl accounts have columns named 'xxxxSegment1',
'xxxxSegment2',
'xxxxSegment3',
'xxxxSegment4'.

I've got the table names and column names in a table. There are 496 of them. The 'xxxx' part is consistent, so, as an example, we've got

TableName, Seg1ColName, Seg2ColName, Seg3ColName, Seg4ColName
--------------------------------------------------------------------------
ARClass,FinanceSegment1,FinanceSegment2,FinanceSegment3,FinanceSegment4
ARClass,Segment1,Segment2,Segment3,Segment4
ARInvoiceDetail,Segment1,Segment2,Segment3,Segment4
ARInvoiceHeader,Segment1,Segment2,Segment3,Segment4
ARPaymentHeader,ARSegment1,ARSegment2,ARSegment3,ARSegment4
ARPaymentHeader,PaymentSegment1,PaymentSegment2,PaymentSegment3,PaymentSegment4

I've also got a mapping table with the current segment values and the new ones.

I want to write an update query that updates the old segment values to the new ones in each table, so one would be:

UPDATE [dbo].[ARInvoiceHeader]

SET	[Segment1] = [gau].[NewSegment1Value]
	[Segment2] = [gau].[NewSegment2Value]
	[Segment3] = [gau].[NewSegment3Value]
	[Segment4] = [gau].[NewSegment4Value]

FROM [dbo].[ARInvoiceHeader] AS [aih]

	JOIN [JHMR_Custom].[900_Utility].[GLAccountUpdate] AS [gau]
		ON [aih].[Segment1] = [gau].[OldSegment1Value]
			AND [aih].[Segment2] = [gau].[OldSegment2Value]
			AND [aih].[Segment3] = [gau].[OldSegment3Value]
			AND [aih].[Segment4] = [gau].[OldSegment4Value]

I can do it with some very ugly and convoluted text processing to actually write several hundred distinct UPDATE statements, but I'm always looking for more elegant ways to get things done. Since Accounting is likely to change their minds multiple times as to just what they want changed to what, I'd like a way that doesn't require all the convolution!





-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[RRinTetons]

Is this job for the MERGE statement? I've never really used it much, but maybe?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[Olaf Doschke]

MERGE is no solution, it merely adds the capability to do inserts and updates az once, but since you only want to update data the UPDATE sql query is the right choice.

There are some helper procedures, for example sp_msforeachtable, but essentially you will generate masses of such Update queries.

Your problem is not the lack of better T-SQL commands, but the lack of a better table design. Databases are about structuring your data and your company fails at that miserably, as it seems. Sorry to sa so, but there is not only a theroy of normallization, it's best practice. A database also is not a document where you can earch&replace everywehre, you have to know where specific things are stored, which table(s), whih field(s) to address. If there are many you ave redundancy, and that is the thing to avoid at all costs. There are only three types of redundancy tolerable: 1) foreign keys pointing to referenced data. As they ar meta data their redundancy of course is allowed, in a 1:N relation many records may point to a parent. 2) backups, of course you are allowed to do backups (by the way in MSSQL this is mainly about log backups, despite in simple recover model). 3) CDC (change data capture) or more genral audit trail. Another form of logs of any data modifications. Advantage over the system transaction log is, you cannot only revert the whole db to a certain point in time, you can pick whatever lost data of a single table, record or field, depending on what audit trail code used. For example CDC logs tables you specify by copying full records into table copies it creates in a schema cdc.

Forgive me, if I forgot further good reasons, but in the database itself you design to have one value in exactly one place, so you don't need mass updates, if something changes. That's the essential idea about data normalization.

Bye, Olaf.

 

[RRinTetons]

Dear Olaf,

After 35 years working with databases I'm pretty familiar with normalization. If this were a database I had designed I promise you it wouldn't have this problem, but it's not. I'm trying to complete a task assigned to me, as it was assigned, in the world in which it was assigned.

I was hoping for a suggestion on how to do that. If you have any such useful suggestions I'd be happy to hear them. Otherwise I'll just go on and get the job done and get my check on payday.

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[Olaf Doschke]

Well, I pointed to sp_msforeachtable and as an oldtimer you should be aware of sysobjects and informationschema to dynamically determine fields, but in the end you'll generate a batch of updates for sure.

Bye, Olaf.

 

[philhege]

Well. This isn't overly convoluted IMHO.

create table #GLAccountUpdate
(TableName varchar(32), Seg1ColName varchar(128), Seg2ColName varchar(128), Seg3ColName varchar(128)
, Seg4ColName varchar(128))
--------------------------------------------------------------------------
insert into #GLAccountUpdate
SELECT
'ARClass','FinanceSegment1','FinanceSegment2','FinanceSegment3','FinanceSegment4'
UNION SELECT
'ARClass','Segment1','Segment2','Segment3','Segment4'
UNION SELECT
'ARInvoiceDetail','Segment1','Segment2','Segment3','Segment4'
UNION SELECT
'ARInvoiceHeader','Segment1','Segment2','Segment3','Segment4'
UNION SELECT
'ARPaymentHeader','ARSegment1','ARSegment2','ARSegment3','ARSegment4'
UNION SELECT
'ARPaymentHeader','PaymentSegment1','PaymentSegment2','PaymentSegment3','PaymentSegment4'
declare @sql varchar(max)
declare @execsql  varchar(max)
set @execsql = ' '
set @sql = 
'
UPDATE [dbo].[<<tablename>>]

SET	[<<Segment1>>] = [gau].[NewSegment1Value]
	[<<Segment2>>] = [gau].[NewSegment2Value]
	[<<Segment3>>] = [gau].[NewSegment3Value]
	[<<Segment4>>] = [gau].[NewSegment4Value]

FROM [dbo].[<<tablename>>] AS [aih]

	JOIN [JHMR_Custom].[900_Utility].[GLAccountUpdate] AS [gau]
		ON [aih].[<<Segment1>>] = [gau].[OldSegment1Value]
			AND [aih].[<<Segment2>>] = [gau].[OldSegment2Value]
			AND [aih].[<<Segment3>>] = [gau].[OldSegment3Value]
			AND [aih].[<<Segment4>>] = [gau].[OldSegment4Value]
'
select @execsql = COALESCE(@execsql + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@sql,'<<Segment4>>',seg4ColName),'<<Segment3>>',seg3ColName),'<<Segment2>>',seg2ColName),'<<Segment1>>',seg1ColName),'<<tablename>>',TableName),'')
--select @execsql = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@sql,'<<Segment4>>',seg4ColName),'<<Segment3>>',seg3ColName),'<<Segment2>>',seg2ColName),'<<Segment1>>',seg1ColName),'<<tablename>>',TableName)


FROM #GLAccountUpdate

print @execsql
--EXEC @execsql if you're brave and want to run it
DROP TABLE #GLAccountUpdate

-----------
With business clients like mine, you'd be better off herding cats.