Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update with Join Informix 4gl SQL

Status
Not open for further replies.

aft5425

Programmer
Oct 23, 2012
16
0
0
US
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.
 
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?
 
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
 
Hi PHV. I tried your suggestion (and different variations), but i get a syntax error.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top