CODEWITHSUNDEEP
sqlpostgresqljoinmappingsql-update
Tamara Caligari
Tamara Caligari

Reputation: 509

SQL update column from another column

I have a scenario similar to the one in the question here: Replacing a sql column value based on a column another table

I have tried this method using the code below:

update stager."Supplier" set genid=supplierdim.genid
from stager."Supplier" 
join stager.supplierdim on ("Supplier".company_name=supplierdim.company_name)

I am using pgAdmin and I keep getting this error: table name "Supplier" specified more than once.

Is there something I am doing wrong? I need to replace the fields of the table stager."Supplier" with the corresponding fields in stager.supplierdim depending on the company name which is a field in both tables. I am doing this to map the data from one table to another.

Upvotes: 0

Views: 1695

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

Because the question you are referring to is tagged for SQL Server, here is the correct syntax for that database:

update s
     set genid = sd.genid
   from stager."Supplier" s join
        stager.supplierdim sd
        on s.company_name = sd.company_name;

EDIT:

The syntax in MySQL is:

update stager."Supplier" s join
      stager.supplierdim sd
      on s.company_name = sd.company_name;
   set s.genid = sd.genid;

EDIT II:

And generic SQL for this:

update stager.Supplier
    set genid = (select min(genid)
                 from stager.supplierdim sd
                 where Supplier.company_name = sd.company_name
                );

Upvotes: 1

Related Questions