CODEWITHSUNDEEP
mysqlsqlmariadb
big
big

Reputation: 73

Multiple update mysql

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

Upvotes: 1

Views: 195

Answers (3)

Rick James
Rick James

Reputation: 142528

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

Upvotes: 0

Vikas Meena
Vikas Meena

Reputation: 342

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

Upvotes: 0

Grene
Grene

Reputation: 434

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

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

Upvotes: 1

Related Questions