Replace values in a column with values from another column

Question

Replace values in a column with values from another column

royston_pledger

Ten Centuries

Points: 1031
More actions
May 21, 2008 at 4:36 am

#186794

hi,
For example ,
TableA TableB
column1 column2
A X
B Y
C Z
i need to replace values of column1 of TableA with random values of column2 of TableB...lets say
TableA
column1
Y
X
Z
i tried the below query to pick random values
..SELECT TOP 1 column2 FROM TableB ORDER BY NEWID()
But this returns only the first value X for all rows
Pls note that im building this query in a function(myFunction()) and returning it . This function is called in a stored procedure wherein TableA is updated...say
...update TableA set column1=(myFunction())
Would appreciate any help on this ...
roy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Can you specify in detail on what bases you wants to update TableA from TableB? What is the exact relation between these two tables?

Mahesh[/font]

MH-09-AM-8694

royston_pledger Ten Centuries Points: 1031 More actions · Answer 2

There need not be a relation between tableA and tableB. Its just that i dont require the values in tableA. The aim is to do a substitution....

The select query in the function is built dynamically as the table and column names can vary....

In oracle

SELECT * FROM( SELECT column FROM table ORDER BY dbms_random.value )WHERE rownum <2

.....fits the requirement..

Looking for something similar to this in SQL 2000..

roy

royston_pledger Ten Centuries Points: 1031 More actions · Answer 3

I have tried the below query too but get an error message saying that the select query returns more than one result

update dbo.tableA set column1=(select column2 from dbo.tableB where (abs(cast((BINARY_CHECKSUM(column2,NEWID())) AS INT))%50)<50)

Using "select top 1"....replaces the values of column1 in tableA with the first value of column2 in tableB....which is not the requirement.

Pls provide suggestions as to how go about doing such a substitution kind of thing..

Thanks

Roy

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

This will do it... As always, details are in the comments...

--===== Supress the auto-display of rowcounts for appearance

SET NOCOUNT ON

--===== Create two test tables (table variables, in this case)

DECLARE @TableA TABLE (Column1 VARCHAR(5))

DECLARE @TableB TABLE (Column2 VARCHAR(5))

--===== Populate TableA with seemingly random data

INSERT INTO @TableA (Column1)

SELECT 'A' UNION ALL

SELECT 'B' UNION ALL

SELECT 'H' UNION ALL

SELECT 'C' UNION ALL

SELECT 'B' UNION ALL

SELECT 'H' UNION ALL

SELECT 'A' UNION ALL

SELECT 'E' UNION ALL

SELECT 'G' UNION ALL

SELECT 'A' UNION ALL

SELECT 'G' UNION ALL

SELECT 'F' UNION ALL

SELECT 'D'

--===== Populate TableB with not so random data

INSERT INTO @TableB (Column2)

SELECT 'U' UNION ALL

SELECT 'V' UNION ALL

SELECT 'W' UNION ALL

SELECT 'X' UNION ALL

SELECT 'Y' UNION ALL

SELECT 'Z'

--===== This is like a calculated TOP statement. Need to do this

-- because SQL Server 2000 doesn't have a calculated TOP statement.

DECLARE @MyCount INT

SELECT @MyCount = COUNT(*) FROM @TableA

SET ROWCOUNT @MyCount

--===== Update table A with data from Table B in a random fashion

UPDATE @TableA

SET Column1 = d.Column2

FROM @TableA a,

(--==== Generates unique combo's of TableA and TableB in random order

SELECT TOP 100 PERCENT --Need this so can include order by in subquery

a.Column1, b.Column2

FROM @TableA a

CROSS JOIN @TableB b

ORDER BY NEWID()) d

WHERE a.Column1 = d.Column1

--===== Set things back to normal for "top"

SET ROWCOUNT @MyCount

--===== Display the results

SELECT * FROM @TableA

By the way, Roy... you might get more answers quicker if you posted table creation and population code like I did. See the URL in my signature for more information.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

royston_pledger Ten Centuries Points: 1031 More actions · Answer 5

Thanks Jeff...that code snippet did work...:)

But when my tableA had 6 distinct records and tableB had 7 distinct records...i find that only three values from column2 of tableB were used repeatedly for substituting column1 values in tableA....

Pls find below the exact scenario (with the exact code)that im facing...would appreciate your help:

Im trying to substitute values in column 'NAME' of table 'tableA' with values of column 'STATE' of table 'tableB'

This has to be done by the stored procedure and user defined function. The code is as below.

The table name 'tableA' and its column name 'NAME' is passed to the stored procedure 'myProcedure', wherein i set the names of tableB and its column STATE and call the function 'myFunction' that develops a select query to pick random values from STATE of tableB and finally update the tableA with those values in the stored procedure.

Pls find the code below.

------------------------------------------------------------------

Code to create tableA:

create table tableA(NAME varchar(500),EMAIL varchar(500))

--populating the tableA

insert into tableA(NAME,EMAIL) values(denis,denis@gmail.com)

insert into tableA(NAME,EMAIL) values(richie,richie@gmail.com)

insert into tableA(NAME,EMAIL) values(tapay,tapay@gmail.com)

insert into tableA(NAME,EMAIL) values(rose,rose@gmail.com)

-----------------------------------------------------------------

Code to create tableB:

create table tableB(NAME varchar(500),STATE varchar(500))

--populating the tableB

insert into tableB(NAME,STATE) values(ron,alabama)

insert into tableB(NAME,STATE) values(crown,slovakia)

insert into tableB(NAME,STATE) values(wang,mississipi)

insert into tableB(NAME,STATE) values(don,vegas)

insert into tableB(NAME,STATE) values(sean,detroit)

-----------------------------------------------------------------------------

Code of stored Procedure:

CREATE PROC myProcedure @tableA_name varchar(30),@tableA_column varchar(30)

AS

BEGIN

DECLARE @param1 varchar(50),--this is the table name 'tableB'

@param2 varchar(50),--this is the column name 'STATE'

@param3 varchar(50),

@functionString varchar(1000),

@updateQuery varchar(1000)

SET @updateQuery = 'update '+@tableA_name+' set '

SET @param1 = 'tableB';

SET @param2 = 'STATE';

SET @param3 = '';

-- calling myFunction to biuld the random select query

SET @functionString = '(myFunction('''+@param1+''','''+@param2+''','''+@param3+''')),';

-- query to update column 'NAME' 'tableA' with random values returned by myFunction

SET @updateQuery = @updateQuery + @tableA_column +'='+@functionString;

EXECUTE(@updateQueryFinal);

END

------------------------------------------------------------------------------------------------

Code to create function:

CREATE FUNCTION myFunction(@param1 VARCHAR(50),@param2 VARCHAR(50),@param3 VARCHAR(50))

RETURNS varchar(1000)

BEGIN

DECLARE@sql_string varchar(1000)

--building the query to return a random values from column(@param2) of table(@param1)

SET @sql_string = 'SELECT TOP 1 '+@param2+' FROM dcipher.'+@param1+' ORDER BY NEWID()';

RETURN (@sql_string);

END

GO

--------------------------------------------------------------------------------------------------

Code Calling the stored procedure from jdbc:

CallableStatement csmt = con.prepareCall("{call myProcedure(?,?)}");

//passing the values 'tableA' and 'NAME' to the procedure parameters @tableA_name and @tableA_column

csmt.setString(1,'tableA');

csmt.setString(2,'NAME');

int result = csmt.executeUpdate();

--------------------------------------------------------------------------------------------------

The problems that i face are:

1. the select query (@sql_string) in the function 'myFunction' - will result in selecting only the top value in STATE of tableB for all values of NAME in tableA. I need different values selected randomly

2. the @updateQuery in the 'myProcedure' updates the column 'NAME' of 'tableA' with the select query (@sql_string) returned by 'myFunction' rather than executing the statement.

3. the tables can have upto 100,000 records...in that case performance too matters..

Pls provide your suggestions

Thanks in advance..

royston_pledger Ten Centuries Points: 1031 More actions · Answer 6

Hi,

Does using CROSS JOIN affect performance when there more than 100 hundred records in the table?

Or is there any workaround for this ..like indexing or temporary tables?