incremental update in insert ... on duplicate key
Is there any way to do an incremental update in an on duplicate key update insert in mysql?
Example w/ error:
inse开发者_JS百科rt into blah (user_id, prefix, email_id, field, source)
select user_id, substring(name, 1, 3), contact_email_id, 2, source from address_book
on duplicate key update source = source + values(source);
ERROR 1052 (23000): Column 'source' in field list is ambiguous
NO, you just cannot specify source = source + values(source);
because source table blah
is not included in the select
, using alias won't fix.
A workaround will be using left join
insert into blah (user_id, prefix, email_id, field, source)
select
ab1.user_id, substring(ab1.name, 1, 3), ab1.contact_email_id, 2,
if(ab2.source, ab1.source+ab2.source, ab1.source)
from
address_book ab1
left join
blah1 as ab2
on
ab1.user_id=ab2.user_id
on duplicate key
update source = values(source);
/* values(source) = ab1.source+ab2.source if duplicate found */
beware on 1:N
relation
What if you prefix the table to the column name, i.e. blah.source, address_book.source?
精彩评论