Multiple update mysql

Question

I have a problem with the following query:

insert into table
select *
from table
on duplicate key update field1 = field1 + 10

I'm trying to update a field in 1 table, of course I cannot use UPDATE statement because I need to update all the rows in the table. When I try to execute the query mysql returns me the error:

ERROR 1052 (23000): Column 'field1' in field list is ambiguous

"table" and "field1" are example names

--UPDATE--

the query could also be:

insert into table
select *
from table
where field2 < 1000
on duplicate key update field1 = field1 + 10

I even tryed:

update table
set field1 = field1 + 10
where field2 < 1000

But all the rows updated have field1 = 10, the sum doesn't work

What's wrong with `update table set field1 = field1 + 10`? Without a WHERE clause, that'll update all the rows. — Wyzard, May 20 '17 at 07:07
you should specify filed1 belong which table, because both of them has field1 — Mohammad Joneidi, May 20 '17 at 07:10
@mj.84 exactly, is the same field of the same table to be updated — big, May 20 '17 at 07:21
@Wyzard I cannot use update table because I need a where clause in other queries — big, May 20 '17 at 07:23
Then use a WHERE clause in your other queries, but don't use it in this one. Your reasons for avoiding UPDATE don't make any sense to me. — Wyzard, May 20 '17 at 07:25
@big Find a answer here [http://stackoverflow.com/questions/3432/multiple-updates-in-mysql?rq=1] — Rohit Kumar, May 20 '17 at 08:01
If there is any chance that field1 isnull you should change the the sum to ifnull(field1,0) + 10 apart from that the update query looks good to me, — P.Salmon, May 20 '17 at 08:07

score 1 · Answer 1 · answered May 20 '17 at 09:22

1

"table" is special/reserved word in SQL. Change it to different name ex. "mytable"

UPDATE mytable
SET 
  field1 = field1 + 10
WHERE 
  field2 < 1000;

answered May 20 '17 at 09:22

Grene

434
6
18

score 0 · Answer 2 · answered May 20 '17 at 13:41

0

I think it can be done by using the name table as well, anyways here is the answer using Insert to do it.

INSERT INTO table (id, field1, field2) VALUES(1, "value1","value2") ON
DUPLICATE KEY UPDATE field1= field1+10 WHERE field2 < 1000

answered May 20 '17 at 13:41

Vikas Meena

322
3
20

score 0 · Answer 3 · answered May 21 '17 at 17:57

0

Which field1 do you want? The one from the SELECT or the column in the table being updated?

See the documentation about when to use this instead of what you have:

... UPDATE field1 = VALUES(field1) + 10

answered May 21 '17 at 17:57

Rick James

135,179
13
127
222

Multiple update mysql

3 Answers3