column {table_name} of relation {table_name} does not exist SQL state: 42703

Question

I am getting an error while running an update query with table name specified along with column name:

UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'

This is the error:

ERROR:  column "temp" of relation "temp" does not exist
LINE 1:      UPDATE Temp SET Temp.Id='23...
                               ^
********** Error **********

ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24

Answer 1

You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE. This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.

Fortunately, the ever helpful documentation explicitly mentions your case:

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, the solution is to simply remove temp. from the SET clause:

UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'

Notes:

• Are you really storing numbers as text? If yes, why? It is usually a recipe for disaster. For example, how do you prevent something like 'mkjcvnd7y78r3tgbhvcjh' entering your id column?
• The way you are using object names starting with capital letters is confusing. Without double-quoting its name, your table in reality is called temp as opposed to Temp. Using it the latter way may decrease readability (depending on your preferences and habits, of course).

Answer 2

What would happen in the case of doing an update for 2 tables that have the same field name?

update car c, airplane a
set c.nr_rute = 1
set a.nr_rute = 1