Question 21366 in Mysql Question posted in
The official MySQL documentation can be found here.

Update table in one schema based on another schema table column in same database – Mysql

I have two schemas in the same database. I want to update a table in one schema based on the table in another schema where the column value matches.
The structure of my database is as follows.

structure of database

As an example, this is my table named "p" in 1st schema that is "public" and have column "id" and "name"

id  name
3   Loral
1   Kim
2   Shawn

and this is the second table named "t" in the second schema that is "t_sc" and have column "id" and "name" but the names are different than table "p"

id  name
1   kylie
3   deny
2   tom

Now I want to update table "p" names according to table "t" names where the id matches.

I have tried the following query

update p set p.Name = t_sc.t.Name WHERE p.ID = t_sc.t.ID

but got the following error

ERROR: missing FROM-clause entry for table "t"

I have tried multiple other ways too but I am not able to get the desired result. I am using Navicat for query and the database is Postgresql.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have found the answer myself. So I thought maybe I should post it for future visitors.

    The query will be

    UPDATE public.p a
SET name=b.name
FROM t_sc.t b
WHERE a.id=b.id

    I was using the alias with column name p.Name of the Table which I want to update. Also I was using the schema name at the wrong time t_sc.t.Name It was causing the error.


  2. 0

    If this is really MySQL, you need to call your schema(s) specifically.

    You can thank this user for the example below:

    Update a column on a table from a table on another schema MySql

    update schema1.table1
inner join schema2.table2
on schema1.table1.IDColumn = schema2.table2.IDColumn
set schema1.table1.column1 = schema2.table2.column2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search