CODEWITHSUNDEEP
sqlsql-server
avnic
avnic

Reputation: 3351

Update 2 big tables

I need to make a stored procedure to update to 2 big tables.

Table 1 is the items table and table 2 is the barcodes table.

I have 100000 items that I need to connect to barcodes. I want to have Barcode inserted in Table 1, and I want to have filled 'Is taken' when the Barcode is in Table 1.

Table 1

 Id |Name   | Barcode
 1    item1    NULL
 2    Item2    Null

Table 2

Id |Barcode| isTaken | ItemId
14    123      0        NULL
321    321      0        NULL

What will be the best approach to update table 1 with free barcode (istaken = 0) and update itemId in table 2 with the item that take his barcode?

Example of my desired result:

Table 1

 Id |Name   | Barcode
 1    item1    123      
 2    Item2    321

Table 2

Id |Barcode| isTaken | ItemId
14    123      1        Item1
321    321      1        Item2

Please help

Upvotes: 0

Views: 65

Answers (3)

dani herrera
dani herrera

Reputation: 51715

Because you can't join by id, you can join by row_number:

begin transaction; set transaction isolation level serializable;

with tt1 as
( select row_number() OVER ( order by id ) as n1, 
         Name, id as id1, Barcode as Barcode1
  from t1
),
tt2 as 
( select row_number()  OVER ( order by id ) as n2, 
         Barcode as Barcode2, id as id2, ItemId
  from t2
  where isTaken = 0
),
j as 
( select id1, id2, Name, Barcode2, ItemId, Barcode1
  from tt1 inner join tt2 on tt1.n1 = tt2.n2 )
update j
  set j.ItemId = j.name;

with tt1 as
( select row_number() OVER ( order by id ) as n1, 
         Name, id as id1, Barcode as Barcode1
  from t1
),
tt2 as 
( select row_number()  OVER ( order by id ) as n2, 
         Barcode as Barcode2, id as id2, ItemId
  from t2
  where isTaken = 0
),
j as 
( select id1, id2, Name, Barcode2, ItemId, Barcode1
  from tt1 inner join tt2 on tt1.n1 = tt2.n2 )
update j
  set j.Barcode1 = j.Barcode2;

commit;

Disclaimer: Not tested.

Upvotes: 0

Biswabid
Biswabid

Reputation: 1411

You can't update both tables at once.
you can do it like this(Cosidering ID is PK in Table1 and FK in Table2):

BEGIN TRANSACTION;

    UPDATE A
    SET A.Barcode= B.Barcode
    FROM Table1 A
    INNER JOIN Table2 B
      ON A.Id=B.Id
    WHERE IsTaken=0

    UPDATE B
    SET B.ItemId= A.Name,
        B.IsTaken=1
    FROM Table1 A
    INNER JOIN Table2 B
      ON A.Id=B.Id
    WHERE IsTaken=0

COMMIT;

Upvotes: 2

Mandar Jogalekar
Mandar Jogalekar

Reputation: 3281

The query could be something like this..

update table1 set Barcode=(select top 1 Barcode from Table2 where isTaken is NULL) where Id=(select top 1 Id from table1 where Barcode is NULL)

Don't have sql with me now.. to check syntax. This needs to go in cursor or while loop to do update to entire table.

Upvotes: -1

Related Questions