CODEWITHSUNDEEP
mysqlsql
zan
zan

Reputation: 95

insert, update, delete statements in a query

I have the following tables: Persons, Person_Categories and Persons_PersonCategories which is a linking table for a n:m relationship.

I've also written the following query, but the insert statement doesn't work correctly. Insert, update and delete are separate statements. The error message I get is "Column count doesn't match value count at row 1"

SELECT Persons_PersonCategories.PersonID, PrsCategory
FROM Person_Categories
INNER JOIN Persons_PersonCategories
ON Persons_PersonCategories.PrsCatID = Person_Categories.PrsCatID

INSERT INTO Persons_PersonCategories
VALUES (:PrsCategory)

UPDATE Persons_PersonCategories
SET Persons_PersonCategories.PrsCatID = :PrsCatID
WHERE Person_Categories.PrsCatID = :OLD PrsCatID
AND Persons.PersonID = :OLD PersonID

DELETE FROM Persons_PersonCategories
WHERE Person_Categories.PrsCatID = :PrsCatID
AND Persons.PersonID = :PersonID;

Any help will be appreciated, zan

Upvotes: 0

Views: 179

Answers (2)

pdjota
pdjota

Reputation: 3243

Looks like you need to add the PersonID

INSERT INTO Persons_PersonCategories(PersonID, PrsCatID)
VALUES (:PersonID, :PrsCatID)

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270677

Specify the column being updated:

INSERT INTO Persons_PersonCategories (PrsCategory) VALUES (:PrsCategory)

Substitute the correct column name in place of PrsCategory if I have not guessed correctly what you're trying to do.

Upvotes: 1

Related Questions