Update related table (Postgres)

Question

I am Creating two table in Postgres.

1st table have one primary column and other columns & in 2nd table have one primary column "MLeaseId" those column same in 1st table (not primary of 1st table) and others column.

How I copy data from 1st table column to second column data?

CREATE TABLE leasetype (
  "LeaseTypeId" integer  NOT NULL ,
  "MLeaseId" character varying(45) NOT NULL,
  "Resident" integer NOT NULL,
  "Business" integer NOT NULL,
  "RArea" float  NULL,
  "BArea" float  NULL,
  "RRent" double  NULL,
  "BRent" double NULL,
  PRIMARY KEY (LeaseTypeId)
);

CREATE TABLE masterlease (
  "SrNo" integer unsigned NOT NULL,
  "MLeaseId" varchar(45) NOT NULL,
  "ExtId" integer unsigned NOT NULL,
  "MPlotNo" character varying(45) NOT NULL,
  "DPlotNo" character varying(45) NOT NULL,
  "SheetNo" character varying(45) NOT NULL,
  "NastiNo" character varying(45) NOT NULL,
  "Date" date NOT NULL,
  "LHolderName" character varying(45) NOT NULL,
  "PhoneNo" character varying(45) DEFAULT NULL,
  "TotArea" double NOT NULL,
  "LeaseDuration" float NOT NULL,
  "UseTypeId" int(6) NOT NULL,
  "LFromDate" date NOT NULL,
  "LToDate" date NOT NULL,
  "OrderDtl" text,
  "Remark" text,
  "Address" character varying(300) NOT NULL,
  PRIMARY KEY (MLeaseId)
)

`INSERT INTO ... SELECT` ? `UPDATE` ? `UPDATE ... FROM` ? Please take a look at the user manual. If you're still stuck, show some sample rows and how you want the result to look. — Craig Ringer, May 21 '13 at 11:49

score 1 · Answer 1 · answered May 29 '13 at 00:13

You can Insert data by selecting it from other tables (by any query at all, you'll just have to make sure that the data being returned by the query is the same of the insert parameters. If I understand correctly you want the line that is being added to masterlease, to have the "MLeaseId" column a mix of various leasetype columns.

Try this:

INSERT INTO masterlease(
    "SrNo", 
    "MLeaseId"
    )
SELECT 
    123,
    concat(leasetype."MLeaseId",'any_code', leasetype."RArea")::varchar as MLeaseId
FROM
    leasetype

Of course you need to add the other values as well. You can do it manually like I did in "SrNo" or you can join other tables in the SELECT statement.

Don't forget that the columns type of the INSERT INTO statement must match exactly with the SELECT statement. You can enforce that by casting (::varchar for example).

Update related table (Postgres)

1 Answers1