4

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
9

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).
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy