Is it possible to UPDATE a table based on other tables, I.E join to other tables in the UPDATE.

EXAMPLE (from my understanding so far):
UPDATE t1 SET col = (SELECT col2 FROM t2 WHERE ...)

This will update t1 but if the select doesn't return any results it will assign NULL to col.

What i would like to do is this (don't know the syntax though):
UPDATE t1 INNER JOIN t2 ON t1.col = t2.col2 SET col = 'test'

What you say seems possible ...
It will be useful if you post the table structure and an example of what result you want ..

BTW, you should be able to add an appropriate predicate to avoid setting column to NULL

Hi ApoPen,

It is posible using alias of the table:
UPDATE T1 A SET A.COL1 = (SELECT B.COL1 FROM T2 B WHERE A.COL2 = B.COL2) WHERE A.COL2 IN (SELECT C.COL2 FROM T2 C)

You can also try this:
UPDATE T1 A SET A.COL1 = (COALESCE(SELECT B.COL1 FROM T2 B WHERE A.COL2 = B.COL2), A.COL1)

Please send reply if this solves your problem.

Hope this helps,
Grofaty

Here is another example. I can't post my table structure unfortunatly.

CREATE TABLE t1(
t1_col1 VARCHAR(10) NOT NULL,
t1_col2 VARCHAR(40) NOT NULL,
t1_col3 SMALLINT
);

CREATE TABLE t2(
t2_col1 VARCHAR(40) NOT NULL,
t2_col2 VARCHAR(10) NOT NULL,
t2_col3 VARCHAR(40) NOT NULL,
t2_col4 SMALLINT
);

CREATE TABLE t3(
t3_col1 VARCHAR(40) NOT NULL,
t3_col2 VARCHAR(10) NOT NULL,
);

I want to be able to udate multiple columns with a select, but if the select doesn't match; don't update with NULL. The select is doing multiple joins to other tables to verify an update.

An example update would be:

UPDATE t1 A SET (A.t1_col1,A.t1_col4) = (SELECT B.t2_col2, C.t2_col3 FROM t2 B INNER JOIN t3 C ON C.t3_col1 = B.t2_col2 WHERE A.t1_col3 = B.t2_col4)

My statement the update is setting multiple columns and the select is doing more joins. But the general idea is here. Note: these are also large tables.


thanks