Update with Join Informix 4gl SQL

[aft5425]

using informix db 11.50.FC3
AIX Unix server 6.1 O/S


I have two tables. I want to get values out of one table and update the other table.

Table 1.
first_name
last_name
family_id

Table 2.
first_name
last_name
f_id
create_date
ref_no

I want to update the first_name and last_name columns in Table 1 with the first_name and last_name columns in Table 2, while doing an inner join on family_id and f_id. Only update if first_name and last_name are both null in Table 1 AND if the create date is greater than 1/1/13. Seems fairly straight forward right?

SQL
update table1 a
set a.first_name =
(select b.first_name
from table2 b
where b.f_id = a.family_id
and b.create_date > '01/01/13'
and a.first_name is null
and a.last_name is null
and b.ref_no between 1 and 10
)
set a.last_name =
(select b.last_name
from table2 b
where b.f_id = a.family_id
and b.create_date > '01/01/13'
and a.first_name is null
and a.last_name is null
and b.ref_no between 1 and 10
)

I get an error: 37000(-201), which seems like a generic error message code i get for almost anything.

 

[aft5425]

Here's another version of the sql:

update table1 a
set (a.first_name, a.last_name) =
(select b.first_name, b.last_name from table2 b)
where a.family_id = b.f_id
and b.create_date > '01/01/13'
and b.ref_no between 1 and 10
and a.first_name is null
and a.last_name is null

Of course, the difference is that i'm updating both columns (first_name and last_name) with a single set statement.

But i still get the same generic sql error 37000(-201). Can anyone help?

 

[PHV]

What about this ?
UPDATE table1 SET (first_name,last_name)=
(SELECT first_name,last_name FROM table2 WHERE f_id=family_id AND create_date>'01/01/13' AND ref_no BETWEEN 1 AND 10)
WHERE first_name IS NULL AND last_name IS NULL
AND family_id IN(SELECT f_id FROM table2 WHERE create_date>'01/01/13' AND ref_no BETWEEN 1 AND 10)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aft5425]

Hi PHV. I tried your suggestion (and different variations), but i get a syntax error.

 

[PHV]

And this ?
UPDATE table1 SET (first_name,last_name)=
[!]([/!](SELECT first_name,last_name FROM table2 WHERE f_id=family_id AND create_date>'01/01/13' AND ref_no BETWEEN 1 AND 10)[!])[/!]
WHERE first_name IS NULL AND last_name IS NULL
AND family_id IN(SELECT f_id FROM table2 WHERE create_date>'01/01/13' AND ref_no BETWEEN 1 AND 10)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886