[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

update statement

I'm trying to make an update work in Informix.  The syntax is below but it does not work.  I'm essentially trying to update a table in temp_table where the join on bhrv_invoice_id joins the two tables.
It should be simple but I'm really struggling with it.


update temp_table
set temp_table.load_fuel = temp_load_totals.load_fuel
from temp_table inner join temp_load_totals on temp_table.bhrv_invoice_id = temp_load_totals.bhrv_invoice_id
0
nikotromus11
Asked:
nikotromus11
3 Solutions
 
nikotromus11Author Commented:
Essentially, I have this:

TableA
ID     Value
1         300


TableB
ID     Value
1         0


All I want to do is update table B with the value from Table A based on the id.
0
 
Robert SchuttSoftware EngineerCommented:
I'm not sure about Informix but most databases do allow join in update statements, something like:
update temp_table inner join temp_load_totals on temp_table.bhrv_invoice_id = temp_load_totals.bhrv_invoice_id
set temp_table.load_fuel = temp_load_totals.load_fuel

Open in new window

In general, if you're not updating huge tables a sub-select should also do the job:
update temp_table
set temp_table.load_fuel = (select temp_load_totals.load_fuel
from temp_load_totals where temp_table.bhrv_invoice_id = temp_load_totals.bhrv_invoice_id)

Open in new window

EDIT: note that there's also a difference in how/which records are updated depending on whether or not all ID's are present in both tables.
0
 
DcpKingCommented:
I think Informix uses ANSI-Standard syntax:

UPDATE TableB
SET ValueField = (
SELECT ValueField
FROM TableA
WHERE TableA.ID = TableB.ID);


hth

Mike
0
 
aplusexpertCommented:
I think You have to write subquery suggested by mike.

Please refer this link for more information

http://www.cs.uoi.gr/~pitoura/informix-doc/Informix_Guide_to_SQL-Syntax.pdf

Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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