2

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.

Omaruchan
  • 403
  • 1
  • 5
  • 12
  • the error message you have posted doesn't match the query you are trying to execute – e4c5 Dec 19 '16 at 23:40
  • Possible duplicate of [PostgreSQL: Error: column of relation does not exist](http://stackoverflow.com/questions/24939839/postgresql-error-column-of-relation-does-not-exist) – Evan Carroll Dec 19 '16 at 23:52

1 Answers1

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'

roby
  • 3,103
  • 1
  • 16
  • 14