PostgreSQL Error: column "i" of relation "inventory" does not exist

Question

I have a database where I need to update the data on some table based on another table's registries. Could I get help as in how to do it since I'm a bit stuck with it.

I have this select query as reference:

select i.* 
from transaction as 't' 
inner join transaction_detail as 'td'
on t.id_transaction=td.id_transaction
inner join inventory as 'i'
on i.id_inventory=td.id_inventory
where t.registered_date>='2016-09-01';

And what I want to do is update one field from the table i based on a date from table t, so it would go something like this:

update inventory i
set i.status='cancel' 
from transaction_detail td
inner join transaction t
on t.id_transaction=td.id_transaction
where t.registered_date>='2016-09-01' 
and i.id_inventory=td.id_inventory;

I'm doing this on postgres, any pointers? Should I try doing this with a function?

EDIT: If it helps at all, relations between columns are one to many from transaction to transaction_detail and one to one from transaction_detail to inventory.

the error message you have posted doesn't match the query you are trying to execute — e4c5
– e4c5, Commented Dec 19, 2016 at 23:40
Possible duplicate of PostgreSQL: Error: column of relation does not exist — Evan Carroll
– Evan Carroll, Commented Dec 19, 2016 at 23:52

roby · Accepted Answer · 2016-12-19 23:47:13Z

4

See https://www.postgresql.org/docs/9.3/static/sql-update.html

column_name The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

So you need to change set i.status='cancel' to set status='cancel'

answered Dec 19, 2016 at 23:47

roby

3,2931 gold badge18 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Error: column "i" of relation "inventory" does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related