rana rana - 9 months ago 68
SQL Question

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);

Answer Source

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)>