October 30, 2007 at 9:28 am
I need to change this cursor to update accounts to remove the letter 'P' in the prefix. This is the only example I have but I'm having a time trying to remove instead of Add. Please help.
DECLARE @SQL varchar(2000)
DECLARE @TableNAme varchar(100)
DECLARE @RowCount int
DECLARE @UpdateSQL varchar(2000)
SET NOCOUNT ON
DECLARE csrTables CURSOR FAST_FORWARD LOCAL FOR
select name, 'select '+CHAR(39)+'I'+CHAR(39)+'+ACCOUNT as NewAcct, account as '+name+' FROM '+name+' WHERE left(account, 1)<> '+CHAR(39)+'I'+CHAR(39)+' AND LEN(RTRIM(ACCOUNT)) > 0 AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39) from sysobjects where type='U' and id in (select ID FROM syscolumns where name = 'account')
OPEN csrTables
FETCH NEXT FROM csrTables INTO
@TableName, @SQL
WHILE @@FETCH_STATUS=0
BEGIN
EXEC(@SQL)
SET @RowCount = @@ROWCOUNT
IF @RowCount > 0
BEGIN
PRINT @TableName + '-' + CAST(@RowCount as varchar(1000))
--SET @UpdateSQL = 'UPDATE '+@TableName+' SET ACCOUNT='+CHAR(39)+'I'+CHAR(39)+'+ACCOUNT WHERE left(account, 1)<> '+CHAR(39)+'I'+CHAR(39)+' AND LEN(RTRIM(ACCOUNT)) > 0 AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39)
--EXEC(@UpdateSQL)
--SET @UpdateSQL = ''
END
FETCH NEXT FROM csrTables INTO
@TableName, @SQL
END
close csrTables
DEALLOCATE csrTables
SET NOCOUNT OFF
UPDATE CLMASTER SET FAMILY = ACCOUNT WHERE COMPANY='MAIN' AND LEFT(FAMILY, 1) <> 'I'
Time Worked: 1 Hr(s) 20 Min(s)
October 31, 2007 at 11:21 am
Use this update statement. I did not validate your where condition. But you should be able to remove your P prefix from your account using this.
--SET @UpdateSQL = 'UPDATE '+@TableName+' SET ACCOUNT= CASE WHEN CHARINDEX(''P'',ACCOUNT,0) =1 THEN RIGHT(ACCOUNT,LEN(ACCOUNT)-1) ELSE ACCOUNT END AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39)
Thanks
Prasad Bhogadi
www.inforaise.com
October 31, 2007 at 11:29 am
Thank you so much.... do I need to change this statement as well
DECLARE csrTables CURSOR FAST_FORWARD LOCAL FOR
select name, 'select '+CHAR(39)+'I'+CHAR(39)+'+ACCOUNT as NewAcct, account as '+name+' FROM '+name+' WHERE left(account, 1)<> '+CHAR(39)+'I'+CHAR(39)+' AND LEN(RTRIM(ACCOUNT)) > 0 AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39) from sysobjects where type='U' and id in (select ID FROM syscolumns where name = 'account')
October 31, 2007 at 12:38 pm
I could not really test this, so please run it in your qa environment
DECLARE @SQL varchar(2000)
DECLARE @TableNAme varchar(100)
DECLARE @RowCount int
DECLARE @UpdateSQL varchar(2000)
SET NOCOUNT ON
DECLARE csrTables CURSOR FAST_FORWARD LOCAL FOR
select name, 'select CASE WHEN CHARINDEX(''P'',ACCOUNT,0)=1 THEN RIGHT(ACCOUNT,LEN(ACCOUNT)-1) ELSE ACCOUNT END as NewAcct, account as '+name+' FROM '+name+' WHERE left(account, 1) = ''P'' AND LEN(RTRIM(ACCOUNT)) > 0 AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39) from sysobjects where type='U' and id in (select ID FROM syscolumns where name = 'ACCOUNT')
OPEN csrTables
FETCH NEXT FROM csrTables INTO
@TableName, @SQL
WHILE @@FETCH_STATUS=0
BEGIN
PRINT(@SQL)
--EXEC(@SQL)
SET @RowCount = @@ROWCOUNT
IF @RowCount > 0
BEGIN
PRINT @TableName + '-' + CAST(@RowCount as varchar(1000))
SET @UpdateSQL = 'UPDATE '+@TableName+' SET ACCOUNT= CASE WHEN CHARINDEX(''P'',ACCOUNT,0)=1 THEN RIGHT(ACCOUNT,LEN(ACCOUNT)-1) ELSE ACCOUNT END WHERE left(account, 1) = ''P''AND LEN(RTRIM(ACCOUNT)) > 0 AND COMPANY='+CHAR(39)+'MAIN'+CHAR(39)
PRINT (@UpdateSQL)
--EXEC(@UpdateSQL)
SET @UpdateSQL = ''
END
FETCH NEXT FROM csrTables INTO
@TableName, @SQL
END
close csrTables
DEALLOCATE csrTables
SET NOCOUNT OFF
UPDATE CLMASTER SET FAMILY = ACCOUNT WHERE COMPANY='MAIN' AND LEFT(FAMILY, 1) = 'P'
Prasad Bhogadi
www.inforaise.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply