CODEWITHSUNDEEP
sql-server
jack_humphrey
jack_humphrey

Reputation: 13

How to loop on one table to update another

I need to loop on records in one table and then update another table. But for some reason I cannot get it working.

DECLARE @v_id1 char(10);
DECLARE @v_id2 char(10);

DECLARE cursor1 CURSOR FOR
    SELECT table1.id1, table1.id2 FROM table1;

OPEN cursor1;

FETCH NEXT FROM cursor1 INTO @v_id1, @v_id2;

WHILE @@FETCH_STATUS = 0 
BEGIN
    UPDATE table2 
    SET table2.record_status = TRIM(table2.record_status)+'OK' 
    WHERE table2.id1 = @v_id1 AND table2.id2 = @v_id2;

    FETCH NEXT FROM cursor1 INTO @v_id1, @v_id2;
END

CLOSE cursor1;
DEALLOCATE cursor1;

I would also like to know how to reference the fields in table1 without having to create two variables.

When I try using table1.id1 in the UPDATE WHERE, I get a bound error (The multi-part identifier... could not be bound).

I also tried an example from another post, but got the bound error:

UPDATE Table1 SET status_record='OK'
SELECT id1, id2 FROM Table2
WHERE Table1.id1 = Table2.id1 AND Table1.id2 = Table2.id2

Any help would be appreciated.

Upvotes: 0

Views: 1872

Answers (1)

Milney
Milney

Reputation: 6417

Why not just:

UPDATE t2
SET 
    record_status = TRIM(t2.record_status) + 'OK' 
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.Id1 = t1.Id1
AND t2.Id2 = t1.Id2

Upvotes: 2

Related Questions