Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1946
  • Last Modified:

informix query to update a temp table

trying to up update a temp table using Aqua Data studio conencting to an informix DB

audit1 temp table
mks_prospectid is blank want to update it
ID        mks_prospectid
1        
2
3

client1 temp table
ID        newclientid
1         101
2          111
3           52523

after the below sql is run we want the audit1 temp table to show the same results as newclientid when the both tables are matched by ID - but we get a syntax error?

-- audit1 – temp table
-- client1 – temp table

update audit1
set mks_prospectid = (select newclientid from client1)
inner join client1 on mks_prospectid = clientid



0
JCTDD
Asked:
JCTDD
  • 3
1 Solution
 
jhp333Commented:
The correct query is:

update audit1
set mks_prospectid = (select newclientid
                                       from client1
                                    where audit1.mks_prospectid = client1.clientid)
0
 
jhp333Commented:
OR if you do like to use join:

update audit1
set mks_prospectid = client1.newclientid
from audit1 inner join client1
        on audit1.mks_prospectid = client1.clientid
0
 
jhp333Commented:
It seems that in both queries,

"audit1.mks_prospectid = client1.clientid"

should be modified to:

"audit1.auditid = client1.clientid"

or

"audit1.ID = client1.ID"

depending on the actual name of the ID column.
0
 
JCTDDAuthor Commented:
for this query:
update audit1
set mks_prospectid = (select newclientid
                                       from client1
                                    where audit1.mks_prospectid = client1.clientid)

get err
>[Error] Script lines: 1-3 --------------------------

 [Informix][Informix ODBC Driver][Informix]Cannot modify table or view used in subquery.



another query we use get the same err

update mktngsurvey_bcs
set mks_newclientid = (select newclientid from pro_bcs
                        inner join mktngsurvey_bcs on prospect_id = mks_prospectid
                        and newclientid is not null and newclientid <> '')
where exists  ( select mks_prospectid from mktngsurvey_bcs
inner join pro_bcs on mks_prospectid = prospect_id)


>[Error] Script lines: 1-7 --------------------------

 [Informix][Informix ODBC Driver][Informix]Cannot modify table or view used in subquery.
update mktngsurvey_bcs
0
 
flutophilusCommented:
Attached code snippet does what you say you want to achieve.

F.
create temp table audit1 (
id integer,
mks_prospectid integer);

insert into audit1 (id) values(1);
insert into audit1 (id) values(2);
insert into audit1 (id) values(3);

create table client1 (
id integer,
clientid integer);

insert into client1 values(1,101);
insert into client1 values(2, 111);
insert into client1 values(3, 52523);

update audit1
set mks_prospectid =
(select client1.clientid 
from client1
where client1.id = audit1.id)
where 1=1;

select * from audit1;

id  mks_prospectid
1   101
2   111
3   525253

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now