Q:

Update columns in same table

I want to update BRCD_NEW column in table branches with the condition applied on another column BRCD in the same table, here is my code, but it returns error

single-row subquery returns more than one row

update branches set brcd_new=(
select 
case 
when BRCD between '5000' and '5999' then  CONCAT('PK001',BRCD)
else CONCAT('PK002',BRCD)
end 
from branches);

A:

You don't need the subquery to achieve what you are doing. Use CASE statement to get the value you need and assign in to your column in SET statement:
update branches 
set brcd_new =
    case 
        when BRCD between '5000' and '5999' then  CONCAT('PK001',BRCD)
        else CONCAT('PK002',BRCD)
    end 
-- WHERE <your filters (if needed)>

