CODEWITHSUNDEEP
phpmysqlsqldatabase
Masoud Aghaei
Masoud Aghaei

Reputation: 1221

insert data into several tables with same primary key

I have two tables , std_personal and std_education like this :

std_personal :
+----------------------+
| id |  name | family  |
+----------------------+

std_education :
+----------------------+ 
| related_std |  grade |
+----------------------+

std_education.related_std must be equal to std_personal.id how can I insert data in these two different tables using 1 query ? my problem is that std_personal.id is not exist when I want to insert std_education.related_std :)

Upvotes: 0

Views: 3685

Answers (4)

Arif YILMAZ
Arif YILMAZ

Reputation: 5866

you should either use a stored procedure or use 2 inserts and 1 select commands.

first 

 insert into std_personal (id,name,family) values(1,'testname','testfamily')

second

 select id from std_personal where name ='testname' and family ='testfamily'

third

 insert into std_education (related_std ,grade) values(id_returned_from_db, 'A+')

OR write a stored procedure to do these three steps with one call from PHP

here is the stored procuder

 Create PROCEDURE [dbo].[TWO_INSERTS_WITH_ONE_WUERY]      
                                              (@name nvarchar(50),@surname nvarchar(50),@grade nvarchar(3))
 AS
 BEGIN

 declare @id int;

 insert into std_personal (id,name,family) values(1,@name,@surname)

 select @id=id from std_personal where name =@name and family =@surname

 insert into std_education (related_std ,grade) values(@id, @grade)


 END



 GO

Upvotes: 1

Peter Wooster
Peter Wooster

Reputation: 6089

Looking at your schema you have one piece of personal data and only one pece of educational data. Typically there would be multiple education records for an individual. With that in mind you should be using a one-to-many relationship between std_personal and std_education. If you do that you should

  • Start a transaction
  • insert into std_personal
  • get the primary key
  • insert into std_education using the personal primary key as a foreign key
  • end transaction

You would use a try catch block and rollback if any error occurs.

Upvotes: 1

Zero-dev
Zero-dev

Reputation: 340

you can't do that in one query,i don't think it's possible but may you do like this,

first insert data into std_personal 

insert into std_personal (id,name,family) values(0,'name','family');

then get id from last record in table std_personal

select id from std_personal order by id desc  limit 1;

finally insert data into std_education.

insert into std_education (related_std ,grade) values(id_from_select, 'grade');

Upvotes: 0

TheEwook
TheEwook

Reputation: 11117

You can't insert data in two tables with only 1 query.

You must first add data into your first table std_personal, get the primary key generated and then insert data into your second table std_education with this primary key

You may want to introduce the foreign key concept.

Upvotes: 2

Related Questions