rana rana - 1 month ago 19
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

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