CODEWITHSUNDEEP
sqldatabasems-accessinsert
eshkere111222333
eshkere111222333

Reputation: 133

Append query error

My primary key of Specialization table is complex and consists of two columns: Specialization_id and Specialty_id(which is foreign key of Specialty table). I am going to make insert query by creating a new SPECIALIZATION of an existing specialty. I have some specializations in my table already, primary key for one specialty with id = 1 looks like (1,1) (2,1), with id = 2 looks like (1,2) (2,2). Now I am going to add a new Specialization to Specialization table, but this Specialization should reference to existing Specialty. My idea is 

    INSERT INTO Specialization ( Specialization_name, Specialization_id, Specialty_id )
VALUES( 'Network technologies','(SELECT Max(Specialization_id) FROM Specialization WHERE Specialty_id = '2')+1', '2');

I've tried with '' clauses and without it, still, I am getting errors. What should I do here?

Upvotes: 0

Views: 156

Answers (2)

Erik A
Erik A

Reputation: 32642

Your main error is the quotes, since your subquery is quoted it will get processed as a string and not a query.

Also, you don't need to use a subquery if you don't have a main query:

INSERT INTO Specialization ( Specialization_name, Specialization_id, Specialty_id )
SELECT 'Network technologies' As a, Max(Specialization_id)+1 As b, 2 As c
FROM Specialization WHERE Specialty_id = 2

However, like dstudeba said, relying on autonumbers for creating new IDs is probably smarter.

Upvotes: 1

dstudeba
dstudeba

Reputation: 9038

In a database like MySQL or ORACLE you should not programatically increment the primary key. Instead you should set the field to AUTOINCREMENT when you design the database.

That would change your INSERT statement to:

 INSERT INTO Specialization (Specialization_name, Specialty_id)
 VALUES('Network technologies', '2')

which is not only much easier, it is safer.

edited to add: Are you sure Specialty_id is a string? I would think it would be some kind of number.

Upvotes: 2

Related Questions