Calling SQL Guys --loveindia

[CheGuevara]

rendu tables ki relation ledu kabatti try this:

 

UPDATE table_a a      

SET a.DonorName = (select DonorName    from (  select DonorName,rownum   from Table_B ) b   where a.rownum = b.rownum)

i never tried this and i dont know if it works too but give a try.....

[mtkr]

4 minutes ago, SqlHelp45 said:

yeah vere em anna options unnayi aa bro..

em aina conditions unnaya!!?? lyk specific row from Test tbl should update specific row in Donor tbl??

lyk 3rd row in Test tbl should updt frst row in Drug tbl??? 

[SqlHelp45]

no conditions bro.. let's say primary table has 1000 rows with 10 columns and DonorName is one of the col. Test table has only one column DonorName and it has 1000 rows of dummy/fake names. So no conditions any row from test table can replace any row in original table.

[mtkr]

6 minutes ago, SqlHelp45 said:

no conditions bro.. let's say primary table has 1000 rows with 10 columns and DonorName is one of the col. Test table has only one column DonorName and it has 1000 rows of dummy/fake names. So no conditions any row from test table can replace any row in original table.

oka option....

elaagu SP using kaabatti... crte 2 temp tbls with PK identity colmns... load the data into these temp tbls... update these temp tbls by joining on PK colm.. del data frm Drug tbl... load Drug tbl frm this temp tbl...

 

inka options ante i guess somethng lyk using cursors to fetch one rec from tbl1 n other rec frm tbl2 and updating n thn fetchng next rec... so onn...

update stmnt using sub query to join on tbls based on row num..

[allaripidugu]

maaya sql server lo cursor untadho ledho thelidhu. nenithe db2 medha chesthunna

using cursor fetch values of rows one by one from table A and later update table b using fetched values. 

 

[SqlHelp45]

2 minutes ago, mtkr said:

oka option....

elaagu SP using kaabatti... crte 2 temp tbls with PK identity colmns... load the data into these temp tbls... update these temp tbls by joining on PK colm.. del data frm Drug tbl... load Drug tbl frm this temp tbl...

 

inka options ante i guess somethng lyk using cursors to fetch one rec from tbl1 n other rec frm tbl2 and updating n thn fetchng next rec... so onn...

update stmnt using sub query to join on tbls based on row num..

Ikkada client laudey gaalu ki DrugChain data drop cheyaadu antha bro. I already researched about third part tools SQL red-gate generator  lo kuda we need to drop but client gaalu are not okay with this approach. And I need to get this task working idi high priportiy issue morning unchi 3 times meeting ayindhi deni gurinchi..

[CheGuevara]

This should work

 

update Table_a a

set DonorName = 

(select b.DonorName

from

(select DonorName, rownum r

from table_b )b,

(select rownum r,rowid

from table_a)c

where b.r = c.r

and c.rowid = a.rowid);

 

let me Know if you face any issues

[k2s]

1 hour ago, 150bryant said:

rendu tables ki relation lekunda how can you join?

baa neeku SQL ocha ?

[loveindia]

UPDATE DrugChain_1
SET DonorName = (SELECT TOP 1 FROM TABLE_B)
 

Just update to any dummy name antey, annitini okey name ki update chesey man... simple... if you still want 1000 dummy names, tell me if there are 10K records in DrugChain_1 table, will there be 10k records in table_b also???

[loveindia]

1 hour ago, CheGuevara said:

This should work

 

update Table_a a

set DonorName = 

(select b.DonorName

from

(select DonorName, rownum r

from table_b )b,

(select rownum r,rowid

from table_a)c

where b.r = c.r

and c.rowid = a.rowid);

 

let me Know if you face any issues

what is rownum  man??? thats not a keyword in sql server... ROW_NUMBER() it is ...

[150bryant]

39 minutes ago, k2s said:

baa neeku SQL ocha ?

unfortunately..i can write some sql code

[k2s]

12 minutes ago, 150bryant said:

unfortunately..i can write some sql code

[CheGuevara]

25 minutes ago, loveindia said:

what is rownum  man??? thats not a keyword in sql server... ROW_NUMBER() it is ...

Naaku oracle telsu man.. Anduke aa language lo rasanu... Sql server lo appropriate key word for rownum use cheste aipotadi

[SqlHelp45]

Thanks guys for all the help. ee SP create chesyina and it works fine when DrugChain table and Test have same number of records for DonorName.

    DECLARE @TEMP TABLE( rowId INT IDENTITY(1, 1) , DonorName VARCHAR(40))
    DECLARE  @TEMP1 TABLE ( rowId INT IDENTITY(1, 1) , ChainID INT)
    DECLARE @MINROWID INT
    DECLARE @MAXROWID INT
    DECLARE @MINROWID1 INT
    DECLARE @MAXROWID1 INT
    DECLARE @DonorName VARCHAR(40)
    DECLARE @ChainID INT
 
      INSERT @TEMP( DonorName)
      SELECT DonorName FROM TEST
      INSERT INTO @TEMP1 (ChainID)
      SELECT ChainID FROM DrugChain_1
      
            SELECT @MINROWID = MIN(ROWID), @MAXROWID = MAX(ROWID) FROM @TEMP
            SELECT @MINROWID1 = mIN(ROWID), @MAXROWID1 = MAX(ROWID) FROM @TEMP1
            SELECT * FROM @TEMP
            SELECT * FROM @TEMP1
            WHILE @MINROWID <=@MAXROWID
            BEGIN
            SELECT @DonorName = DonorName FROM @TEMP WHERE ROWID = @MINROWID
            SELECT @ChainID = ChainID FROM @TEMP1 WHERE ROWID = @MINROWID1
            UPDATE DrugChain_1 SET DonorName = @DonorName  WHERE ChainID = @ChainID
            SET @MINROWID = @MINROWID +1
            SET @MINROWID1 = @MINROWID1 + 1

 

Ippudey call lo they asked me to tweak the code a bit. Change endi antha lets say DrugChain table has 10,000 rows and Test table has only 1000 rows.   With that 1000 rows DonorName column in Test table should populate values in DrugChain table and the names can repeat.

[SqlHelp45]

ltt

Quote