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 ?
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, '')
INSERT - this is an UPDATE
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)
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
