Update query takes ages

[mpc123]

HI

Hope somebody can help. My query updates a colomn in tab1 from a row in tab2 but only if a colomn in tab1 is equal to that of a row in tab2.

tab 1 has 360000 rows
tab 2 has 477 rows

The update query runs for ages and still does not complete or when I execute it seems nothing has been updated.
Hopefully we can make this quicker. Thanks

I currently have this update query

update tab1, tab2 set _tab1.info = tab2.info where tab1.info2 = tab2.info2;

[Ewald Horn]

Indexes...

You do not have indexes on the columns you are comparing, so it will be doing table scans all the time.

Simply create indexes on both tables, for those columns, before you run the query.

[mpc123]

Great thanks for the quick reply, stange i already use indexes on those tables but looking at the indexes set they do not include those 2 colomns I am working on.

Thanks again for the heads up

[Reverend Jim]

Try this query instead

UPDATE tab1
   SET tab1.info  = tab2.info
  FROM tab1 INNER JOIN tab2
    ON tab1.info2 = tab2.info2

[Ewald Horn]

I've run into this a few times - and the good Reverend Jim has a nice solution, but it will still benefit from indexing.