Link to home
Start Free TrialLog in
Avatar of JCTDD
JCTDD

asked on

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



Avatar of jhp333
jhp333
Flag of United States of America image

The correct query is:

update audit1
set mks_prospectid = (select newclientid
                                       from client1
                                    where audit1.mks_prospectid = client1.clientid)
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
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.
Avatar of JCTDD
JCTDD

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of flutophilus
flutophilus

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial