Update only rows where cust_id is present in other table

Question

I have a query that will present a list of customers to be anonomized:

select cu_number into #_t
from customer
where cu_first_name is not null

I want to update last_name to " 'Anonomized' + sequence " and try this:

update customer
  set cu_last_name = 'Anonomized' + convert(varchar, cu_number)
where cu_number = (select * from #_t);

I get the following error, and can't seem to find a suitable way around this:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Anyone with an idea on how to improve the subquery?

/Andreas

why do you need a temp table? why not do this `update customer set cu_last_name='Anonomized' + convert(varchar(), cu_number) WHERE cu_first_name is not null` — ughai, Jun 03 '15 at 11:22
Try using where exists instead of comparing cu_number using a select statement — Tom, Jun 03 '15 at 11:24
I have simplified the question a bit. The reason I have a temporary table is because I need to be able to add conditions to the initial select, and I'm to lazy to replace in all the steps. At the time I have 15 updates going to different tables where 'cu_number' is a common denominator. (i.e. in table address ad_customer is the same as cu_customer). @jarlh I will try 'IN' later. Seems like that should work.. — Andreasp, Jun 04 '15 at 13:46

Giorgi Nakeuri · Answer 1 · 2015-06-05T07:34:36.550

2

I think you don't need temp table at all:

update customer set cu_last_name='Anonomized' + convert(char, cu_number)
where cu_first_name is not null

In MySQL you can not use convert function with varchar type.

For Sql Server:

update customer set cu_last_name='Anonomized' + convert(varchar(10), cu_number)
where cu_first_name is not null

edited Jun 05 '15 at 07:34

answered Jun 03 '15 at 11:23

Giorgi Nakeuri

35,155
8
47
75

OP's code looks like Transact-SQL to me. At the very list, `CONVERT(type, value)` is not a MySQL syntax, [which would be `CONVERT(value, type)` instead](https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#function_convert). – Andriy M Jun 05 '15 at 07:20
I don't remember bu probably I have answered this question quickly before post was edit. or may be there was some comment which was deleted. – Giorgi Nakeuri Jun 05 '15 at 07:33
Hi, I'm sorry. I have mixed my tags. I'm quite new to this and still learning. Thank you for your response! – Andreasp Jun 11 '15 at 04:38

score 0 · Accepted Answer · answered Jun 11 '15 at 04:44

Since the answer was delivered in a comment, I thought I'd add it as an answer. Adding "IN" instead of "=" solved the issue I had.

where cu_number IN (select * from #_t);

Thank you, @Jarlh! And thank you to the rest as well for great tips going forward.

Update only rows where cust_id is present in other table

2 Answers2