MySQL: How to Update values into multiple rows and use where clause as well?

Question

I am trying to Update an int value (the value of a primary key, retrieved from another table named country) into the city_id column (which is a Foreign Key in the persons table from the country table), in all the rows where the value of the name attribute/column is John, Amy, Adam, Abraham, Bob, David, Robert, George, Elizabeth, Mike and Barbara.

I tried using this query but I get an exception that there is an error in my MySQL syntax.

String query1="update persons set city_id="+rid+" where "
                + "(name= 'John' and "
                + "name= 'Amy' and "
                + "name= 'Adam' and "
                + "name= 'Abraham' and "
                + "name= 'Bob' and "
                + "name= 'David' and "
                + "name= 'Robert' and "
                + "name= 'George' and "
                + "name= 'Elizabeth' and "
                + "name= 'Mike' and "
                + "name= 'Barbara' )";

I tried this by removing the parenthesis as well.

So can somebody point out what's wrong with it, or another query which can serve my purpose?

This is some sample data. You can see the names, the table names and the important key names are mentioned as well. One thing to mention is that the `country` table's `city_id` column (which is the `foreign key` in the `persons` table) has only one value (because there is only one `row` in the table) — Solace, Apr 28 '14 at 15:46
Hi you need to use batch for multiple update or insert query ... I done this using java and mybatis framework — Sameer Kazi, Apr 29 '14 at 10:50
for update multiple columns with different where close refer my answer on this question http://stackoverflow.com/questions/17928799/how-to-implement-batch-operations-with-mybatis-spring/18634322#18634322 — Sameer Kazi, Apr 30 '14 at 03:26

score 1 · Answer 1 · answered Apr 28 '14 at 13:15

1

you could use

String query1="update persons set city_id="+rid+" where 
name= 'John' or 
name= 'Amy' or 
name= 'Adam' or 
name= 'Abraham' or 
name= 'Bob' or 
name= 'David' or 
name= 'Robert' or 
name= 'George' or 
name= 'Elizabeth' or 
name= 'Mike' or 
name= 'Barbara'";

or you could use

String query1="update persons set city_id="+rid+" where name in ('John','Amy','Adam','Abraham','Bob','David','Robert','George','Elizabeth','Mike','Barbara')";

answered Apr 28 '14 at 13:15

Jonathan

1,542
3
16
24

I just used the second one. It removes the error but adds the value in only one last row in the city_id column in persons :s – Solace Apr 28 '14 at 13:24

cr7 · Answer 2 · 2014-04-29T16:38:12.110

-1

you should use the following command,and it definitely works

update persons set city_id="+rid+" where name= 'John'
OR name='Amy'
OR name='Adam'
OR name='Abraham'
OR name= 'Bob'
OR name='David'
OR name='Robert'
OR name='George'
OR name='Elizabeth'
OR name='Mike'
OR name='Barbara';

edited Apr 29 '14 at 16:38

answered Apr 28 '14 at 13:32

cr7

1
2

1

how it will definitely works? there is syntax error in the query. you can not write OR condition like this without bracket. – Ilesh Patel Apr 28 '14 at 13:38
yes,we can write an OR condition without using a bracket...im trying to upload the pic in which I executed the command without using the bracket...but I don't have enough reputation to upload a pic – cr7 Apr 28 '14 at 13:53
1

make sql fiddle for that and share link over here. – Ilesh Patel Apr 28 '14 at 14:03
It definitely does not work; that is invalid SQL. You would need something along the lines of `where name= 'John' OR name='Amy' ...` – LittleBobbyTables - Au Revoir Apr 28 '14 at 14:09
@cr7 - and you'll notice that ID 2 doesn't get updated (actually, ID 3 doesn't get updated either, but since its ID is already 3, you can't tell). Try adding a fourth or fifth row and use the method you suggest, and you'll notice they *also* won't update. – LittleBobbyTables - Au Revoir Apr 28 '14 at 14:55
@Littlebobbytables.. yeah.they r not getting updated...thanks a lot – cr7 Apr 29 '14 at 02:17

MySQL: How to Update values into multiple rows and use where clause as well?

2 Answers2