CODEWITHSUNDEEP
sqlmysql
Gaurav Sharma
Gaurav Sharma

Reputation: 2848

Why I am not able to update the column based on a condition which is not the primary key

Why I am not able to update the column based on a condition which is not the primary key.

I am trying to update the constituencies table where name matches a specific criterial as shown below but the below queries shows an error 


Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table constituencies set city_id = '1' where constituencies.name = "East Delhi"' at line 1



update table constituencies set city_id = '1' where constituencies.name = "East Delhi";
update table constituencies set city_id = '1' where constituencies.name = "South Delhi";
update table constituencies set city_id = '1' where constituencies.name = "Delhi Sadar";
update table constituencies set city_id = '1' where constituencies.name = "Karol Bagh";
update table constituencies set city_id = '1' where constituencies.name = "New Delhi";
update table constituencies set city_id = '1' where constituencies.name = "Outer Delhi";
update table constituencies set city_id = '1' where constituencies.name = "North East Delhi";
update table constituencies set city_id = '1' where constituencies.name = "North West Delhi";
update table constituencies set city_id = '1' where constituencies.name = "West Delhi";

Is it necessary that the condition should be checked with a primary key only ?

Please throw some light on the above.

Upvotes: 0

Views: 161

Answers (2)

k.m
k.m

Reputation: 31464

You don't need table keyword, as Salil posted. Also, take a look at function IN for your update condition - it could simplify your queries (as you're updating rows with same ID anyways).

Try:

UPDATE constituencies set city_id = '1' 
WHERE name IN 
(
  "East Delhi", "South Delhi", "Delhi Sadar", "Karol Bagh", "New Delhi", 
  "Outer Delhi", "North East Delhi", "North West Delhi", "West Delhi"
);

Upvotes: 3

Salil
Salil

Reputation: 47512

Try

update constituencies set city_id = 1 where constituencies.name = "East Delhi";

You Don't need to write Table in your mysql Query.

Upvotes: 10

Related Questions