November 16, 2010 at 7:44 pm
Basically I want to know if this will work?
Should i check for records in each table before i update? Or should I stick with this???
/***********************/
/* CREATED BY: ALEX R. */
/* DATE: 11/16/10 */
/***********************/
USE TRANSDB4
DECLARE @POPIDS VARCHAR(MAX)
DECLARE @BRANCHIDS VARCHAR(MAX)
DECLARE @STATEID VARCHAR(MAX)
SET @STATEID = (SELECT '''' + STATE_CODE + '''' FROM COUNTRIES_STATES
WHERE (STATE_NAME = '<STATE NAME, =, NULL>') AND
(STATE_COUNTRY = '<COUNTRY CODE, =, NULL>'))
IF EXISTS(SELECT * FROM BANKS_BRANCHES
WHERE (Bkbr_Country = '<COUNTRY CODE, =, NULL>') AND
(Bkbr_State = @STATEID) AND
(Bkbr_City = '<MISSPELLED CITY, =, NULL>'))
BEGIN
UPDATE BANKS_BRANCHES
SET BKBR_CITY = '<CORRECT NAME, =, NULL>'
WHERE (Bkbr_Country = '<COUNTRY CODE, =, NULL>') AND
(Bkbr_State = @STATEID) AND
(Bkbr_City = '<MISSPELLED CITY, =, NULL>')
SET @POPIDS = ''
SELECT @POPIDS = @POPIDS + BKBR_BANK_ID + '''' + ',' + '''' FROM BANKS_BRANCHES
WHERE (Bkbr_Country = '<COUNTRY CODE, =, NULL>') AND
(Bkbr_State = @STATEID) AND
(Bkbr_City = '<MISSPELLED CITY, =, NULL>')
GROUP BY BKBR_BANK_ID
IF @POPIDS <> ''
BEGIN
SET @BRANCHIDS = ''
SELECT @BRANCHIDS = @BRANCHIDS + BKBR_BRANCH_ID + '''' + ',' + '''' FROM BANKS_BRANCHES
WHERE (Bkbr_Country = '<COUNTRY CODE, =, NULL>') AND
(Bkbr_State = @STATEID) AND
(Bkbr_City = '<MISSPELLED CITY, =, NULL>')
GROUP BY BKBR_BRANCH_ID
UPDATE BANKS_BRANCHES
SET BKBR_CITY = '<CORRECT NAME, =, NULL>'
WHERE (Bkbr_Country = '<COUNTRY CODE, =, NULL>') AND
(Bkbr_State = @STATEID) AND
(Bkbr_City = '<MISSPELLED CITY, =, NULL>')
UPDATE TRANS
SET TR_BEN_CITY = '<CORRECT NAME, =, NULL>'
WHERE (tr_country_code = '<COUNTRY CODE, =, NULL>') AND
(tr_ben_state = @STATEID) AND
(tr_ben_city = '<MISSPELLED CITY, =, NULL>') AND
(tr_bank_id IN ('''' + (LEFT(@POPIDS, LEN(@POPIDS) -2)))) AND
(tr_bank_Branch IN ('''' + (LEFT(@BRANCHIDS, LEN(@POPIDS) -2))))
UPDATE XREF
SET CITYID = '<CORRECT NAME, =, NULL>'
WHERE (CountryID = '<COUNTRY CODE, =, NULL>') AND
(StateID = @STATEID) AND
(CityID = '<MISSPELLED CITY, =, NULL>') AND
(PoPID IN ('''' + (LEFT(@POPIDS, LEN(@POPIDS) -2)))) AND
(BranchID IN ('''' + (LEFT(@BRANCHIDS, LEN(@POPIDS) -2))))
END
ELSE
PRINT 'NO CHANGES WERE MADE TO TABLES: TRANS, XREF'
END
ELSE
PRINT 'NO RECORDS WERE CHANGED'
November 17, 2010 at 2:33 am
aareynaga (11/16/2010)
Basically I want to know if this will work?
It will, but you're issuing unneeded SQL.
Should i check for records in each table before i update? Or should I stick with this???
You don't need to check before updating: just update and check @@ROWCOUNT after that.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply