Updating multiple referance column data seperated by comma.
-
Hello All,
Please give me the shortest way...
I have one table with say...
Table1
tbl1_Col1 int
tbl1_Name char(50)
tbl1_Col1_NEW int
Table2
tbl2_Col1 int
tbl2_Col2 someval char(50)
tbl2_Col3 char(100)....now this column contains values from tbl1_Col1 which are seperated by comma. Now for some reason I want to update these values from tbl1_Col1_NEW column data. Can anybody gives me the shortest way to do this as I am having atleast 10,000 records, and these records needs to be processed in say 10 to 20 secsonly.
Regards,
MaheshB
-
On way would be to use the replace function.
E.g look for the value in table1 col1 and replace with the value table1 col new.
Check BOL for more info.
However, the values need to be unique or you will end up replacing all the instances of the old value with the new value.
------------------------------
The Users are always right - when I'm not wrong! -
I can't understand why you have structured the data in your tables in this way.
Maybe a better approach would be to look at a better design to solve your problem ?
An attribute containing comma seperated values won't bet past first normal form
cheers
dbgeezer
-
give an example of that dataset. I think I can help. Send to anup@semiprecious.com as well.
-
Table1
Col1 Name NewCol1
1 A 11
2 B 12
.......
Table2
Col1 Name table1_Col1
1 SomeVal 1, 2, 3
2 SomeNewVal 2, 3
.....
Now what I want is, I want to replace the values of table2.table1_col1 column with 11, 12, 13 for first row, 12, 13 for second row and so on.
Did you get what I want to say?
Regards,
MaheshB
-
Your violating the rules of normalization. You need a third table to store each atomic value for table1_Col1 . . .
CREATE TABLE [dbo].[Table1] (
[Col1] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NewCol1] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[Col1] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fk_Table1] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table3] (
[Col1] [int] IDENTITY (1, 1) NOT NULL ,
[table1_Col1_Values] [int] NOT NULL ,
[fk_Table2] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Col1]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Table3] ADD
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[Col1]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[fk_Table1]
 
REFERENCES [dbo].[Table1] ([Col1]
 
ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATIONGO
ALTER TABLE [dbo].[Table3] ADD
CONSTRAINT [FK_Table3_Table2] FOREIGN KEY
(
[fk_Table2]
 
REFERENCES [dbo].[Table2] ([Col1]
 
ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATIONGO
-
Try this...
CREATE TABLE [dbo].[Table3] (
[table1_Col1] [char] (1000) NULL ,
[table1_Col1_new] [char] (1000) NULL
)
GO
----------
CREATE TRIGGER table2_update
ON table2
for update
AS
BEGIN
declare @OldCol as varchar(100)
declare @NewCol as varchar(100)
declare @Col as varchar(100)
declare @string as varchar(100)
select @NewCol=''
DECLARE Delete_temp CURSOR
READ_ONLY
FOR select rtrim(ltrim(table1_col1))+ ',' from deleted
OPEN Delete_temp
FETCH NEXT FROM Delete_temp INTO @OldCol
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @string = @OldCol
select @NewCol = ''
WHILE @string<>''
Begin
select @col = substring(@string,1,CHARINDEX(',',@OldCol)-1)
select @string = substring (@string,CHARINDEX(',',@OldCol)+1,1000)
select @NewCol=@NewCol + rtrim(NewCol1) + ',' from table1 where col1=@col
end
insert INTO table3 values( @OldCol,@NewCol)
END
FETCH NEXT FROM Delete_temp INTO @OldCol
END
CLOSE Delete_temp
DEALLOCATE Delete_temp
END
GO
-----------
update table2
set table1_col1=rtrim(table1_col1) + ','
-----------------
DROP TRIGGER table2_update
Go
----------
update table2
set table2.table1_col1=table3.table1_col1_new
from table3
where table3.table1_col1 = table2.table1_col1
-
If you have SQL2000, create a function
CREATE Function dbo.fn_table1 (@col1 int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @result varchar(1000)
SELECT @result = COALESCE(@result + ', ','') + CAST(NewCol1 as varchar)
FROM Table1
WHERE Col1 >= @col1
ORDER BY Col1
RETURN @result
END
UPDATE Table2
SET table1_Col1 = dbo.fn_table1(Col1)
Otherwise use a loop
DECLARE @maxcol1 int,@col1 int,@result varchar(1000)
SELECT @maxcol1 = MAX(Col1) FROM table1
SET @col1 = 0
WHILE (@col1 < @maxcol1)
BEGIN
SET @col1 = @col1 + 1
SET @result = NULL
SELECT @result = COALESCE(@result + ', ','') + CAST(NewCol1 as varchar)
FROM Table1
WHERE Col1 >= @col1
UPDATE Table2
SET table1_Col1 = @result
WHERE Col1 = @col1
END
Not sure about doing it in 10-20 secs though.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply