0

I have two tables which looks like this:

Table1(ID, name, street, houseNo, DeliveryID) and Table2(ID, name street houseNo, DeliveryID)

I want to create a procedure that checks the contents of Table1(name, street, houseNo) and Table2(name, street houseNo). And if the content is equal to each other it should insert Table1.DeliveryID into Table2.DelvieryID.

how would i do that ?

Lahib
  • 1,305
  • 5
  • 34
  • 62

3 Answers3

3

Please try using inner join update (SQL Server Update with Inner Join):

UPDATE Table2
SET Table2.DelvieryID = Table1.DelvieryID
FROM Table2, Table1
WHERE 
    Table2.name = Table1.name AND
    Table2.street = Table1.street AND
    Table2.houseNo = Table1.houseNo

For considering NULL values, try

UPDATE Table2
SET Table2.DelvieryID = Table1.DelvieryID
FROM Table2, Table1
WHERE 
    ISNULL(Table2.name, '') = ISNULL(Table1.name, '') AND
    ISNULL(Table2.street, '') = ISNULL(Table1.street, '') AND
    ISNULL(Table2.houseNo, '') = ISNULL(Table1.houseNo, '')
Community
  • 1
  • 1
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • Some of them are nulls and i can see that i cant compare nulls. How can i get around that issue ? – Lahib Apr 24 '13 at 06:26
  • You asked for an `INSERT` - this is an `UPDATE` – Nick.Mc Apr 24 '13 at 06:29
  • Means you don't need to update DeliveryID if both table columns are NULL? – TechDo Apr 24 '13 at 06:31
  • @techdo no i mean that houseNO in both tables can be NULL. And if that is the case then i wouldnt be able to find the right row. Can i convert the rows with NULLs to something else that can be compared ? – Lahib Apr 24 '13 at 06:34
  • @ElectricLlama you UPDATE the row by inserting Table1.DeliveryID into Table2.DeliveryID – Lahib Apr 24 '13 at 06:36
  • Huh? You do not update a row by inserting it. and the code isn't inserting, its updating!!! Never mind. – Nick.Mc Apr 24 '13 at 06:39
  • @ElectricLlama both rows already exist in the database. So it's not possible to insert it again. Therefor if name, street and houseNo on both table are equal to each other, then you have to update the DeliveryID column in table2. – Lahib Apr 24 '13 at 06:43
  • It's only impossible if there is a unique constraint. I agree it makes no sense to insert a new record. – Nick.Mc Apr 24 '13 at 08:22
3
UPDATE Table2
SET Table2.DelvieryID = Table1.DelvieryID
FROM Table1 t1, Table2 t2
WHERE CHECKSUM(t1.name,t1.street,t1.houseNo)=CHECKSUM(t2.name,t2.street,t2.houseNo)
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
2

Simple joins will work ..

UPDATE tab2
SET tab2.DelvieryID = tab1.DelvieryID
FROM Table2 tab2, Table1 tab1
WHERE 
tab2.name = tab1.name AND
tab2.street = tab1.street AND
tab2.houseNo = tab1.houseNo
Nishad
  • 426
  • 2
  • 7
  • 20