CODEWITHSUNDEEP
sqloracle-database
Zakaro
Zakaro

Reputation: 53

insert into table from two another tables - oracle

i have 3 tables. Two First tables has data and i want 3rd table insert data from that first two.

TABLE A :

CREATE TABLE z_ostan ( id NUMBER PRIMARY KEY,
                     name  VARCHAR2(30) NOT NULL CHECK (upper(name)=name) 
                     );

TABLE B:

CREATE TABLE z_shahr ( id NUMBER PRIMARY KEY,
                     name  VARCHAR2(30) NOT NULL CHECK (upper(name)=name),
                     ref_ostan NUMBER,
                     CONSTRAINT fk_ref_ostan FOREIGN KEY (ref_ostan) REFERENCES z_ostan(id)
                    );

TABLE C:

CREATE TABLE z_shar2 ( shahr_name VARCHAR2(30),
                       ostan_name VARCHAR2(30),
                       payetakht number);

insert data from TABLE A and B into C by this conditions:

i CANT INSERT BY This Conditions on TABLE C

Upvotes: 0

Views: 67

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Looks like a join:

INSERT INTO z_shar2 (shahr_name, ostan_name, payetakht)
   SELECT b.name,
          a.name,
          CASE WHEN a.name = 'somthing' THEN 1 ELSE NULL END payetakht
     FROM z_shahr b JOIN z_ostan a ON a.id = b.ref_ostan

As of payetakht column's value: I initially thought that you, actually, meant when a.name is not null but that can't be as name column is declared as not null, so ... that's probably really (misspelled) somthing.

Upvotes: 1

Related Questions