Rinu Rinu - 1 year ago 58
MySQL Question

Check first 7 charactors of a field has duplicate in sql

I need to update based on the first 7 characters of a field.

For example:
I have filed named rds with multiple values starting from "10-1111". If there are more than one value I need to update another field named "multiorders" with "yes" if there is only a single value i need to update "multiorders" with "no"

rds | multiorders
10-1111-1332 | yes
10-1111-4322 | yes
10-1111-8779 | yes
11-3224-4444 | no
12-4567-3322 | no
16-3354-6645 | no
17-3344-7777 | yes
17-3344-1212 | yes

it should only check the first 7 charactor and update the multiorders with yes or no

Answer Source

You can try query like this:

update y 
set y.multiorders='yes' 
from yourtable y where left(y.rds,7) in (select left(rds,7) as rds from yourtable group by left(rds, 7) having count(*) > 1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download