CODEWITHSUNDEEP
sqlsql-updateinformix
unwitting
unwitting

Reputation: 3396

Update referencing another table

I have a statement that needs writing (with generic names for stuff, since this is for work) to update a column 'updCol' in table 'tUpd'. tUpd also has a column 'linkCol' which is present in another table tOther. tOther has another column 'idCol'.

My problem is to update the updCol value of rows in tUpd which correspond via linkCol to rows with a given idCol value.

One solution I think should work is the following;

update
    tUpd
set
    updCol = XXX
where exists (
    select
        idCol
    from
        tOther
    where
        tOther.linkCol = tUpd.linkCol
    and tOther.idCol = MY_ID
)

However, I have worries that this approach will lead to poor performance, since I've been warned of sub-queries in relation to performance before - this sub-query will be run once for each row of tUpd, is this correct?

Has anyone got a better suggestion?

Important Update: my workplace avoids using SQL JOINs at all costs, preferring to join within the where clauses using, eg, where a.col = b.col. This is arguably rather awkward but allows a flexibility in especially logging which I don't fully understand. SO, I'm looking for non-JOIN-using solutions :)

Upvotes: 2

Views: 8884

Answers (4)

BlueMystic
BlueMystic

Reputation: 2297

This works for Informix Databases:

UPDATE dest_table V 
SET field_1 = 
   (SELECT field_1 FROM source_tbl WHERE field_2 IS NULL
   AND field_1 = V.field_1);

Reference

Upvotes: 1

Dipesh Shah
Dipesh Shah

Reputation: 581

All the above solutions gives an error in Informix as it cannot find the one of the table. Here is a solution for this which worked for me:

update table1
set table1.field2 = (select table2.field2 from table2 where table1.field1 = table2.field1)
where table1.field1 in (select table2.field1 from table2)

edit: A multi-column solution from another question

update table1
set (table1.field2, table2.field3) = (
  (select table2.field2, table2.field3 
     from table2 
     where table1.field1 = table2.field1)
)
where table1.field1 in (select table2.field1 from table2)

Upvotes: 7

Prasanna
Prasanna

Reputation: 4703

Its simply like this

UPDATE DestinationTable
SET DestinationTable.UpdateColumn =  SourceTable.UpdateColumn 
FROM SourceTable
WHERE DestinationTable.JoinColumn = SourceTable.JoinColumn

Upvotes: 2

Robert
Robert

Reputation: 25753

Maybe it will help you

update tUpd
set tU.updCol = XXX
from tOther tot, tUpd tU   
where tot.linkCol = tU.linkCol
  and tot.idCol = MY_ID

Here is link to similar problem.

Upvotes: 2

Related Questions