stored procedures - CASE STATEMENT gives out wrong results in MySQL -
hi using mysql creating new column , adding values using case statement not give desired output like, giving value 1 9 cases .
can please me out mistake making in
alter table del1 add column value float null; update del1 set value = (case when brand "%rs 1 off%" 1 when brand "%rs 2 off%" 2 when brand "%rs 3 off%" 3 when brand "%rs 4 off%" 4 when brand "%rs 5 off%" 5 when brand "%rs 6 off%" 6 when brand "%rs 7 off%" 7 when brand "%rs 8 off%" 8 when brand "%rs 9 off%" 9 when brand "%rs 10 off%" 10 when brand "%rs 11 off%" 11 when brand "%rs 12 off%" 12 when brand "%rs 13 off%" 13 when brand "%rs 14 off%" 14 when brand "%rs 15 off%" 15 when brand "%rs 16 off%" 16 when brand "%rs 17 off%" 17 when brand "%rs 18 off%" 18 when brand "%rs 19 off%" 19 when brand "%rs 20 off%" 20 when brand "%rs 21 off%" 21 when brand "%rs 22 off%" 22 when brand "%rs 23 off%%" 23 when brand "%rs 24 off%" 24 when brand "%rs 25 off%" 25 when brand "%rs 26 off%" 26 when brand "%rs 27 off%" 27 when brand "%rs 28 off%" 28 when brand "%rs 29 off%" 29 when brand "%rs 30 off%" 30 when brand "%rs 31 off%" 31 when brand "%rs 32 off%" 32 when brand "%rs 33 off%" 33 when brand "%rs 34 off%" 34 when brand "%rs 35 off%" 35 when brand "%rs 36 off%" 36 when brand "%rs 37 off%" 37 when brand "%rs 38 off%" 38 when brand "%rs 39 off%" 39 when brand "%rs 40 off%" 40 when brand "%rs 41 off%" 41 when brand "%rs 42 off%" 42 when brand "%rs 43 off%" 43 when brand "%rs 44 off%" 44 when brand "%rs 45 off%" 45 when brand "%rs 46 off%" 46 when brand "%rs 47 off%" 47 when brand "%rs 48 off%" 48 when brand "%rs 49 off%" 49 when brand "%rs 50 off%" 50 when brand "%rs 51 off%" 5 when brand "%rs1 off%" 1 when brand "%rs2 off%" 2 when brand "%rs3 off%" 3 when brand "%rs4 off%" 4 when brand "%rs5 off%" 5 when brand "%rs6 off%" 6 when brand "%rs7 off%" 7 when brand "%rs8 off%" 8 when brand "%rs9 off%" 9 when brand "%rs10 off%" 10 when brand "%rs11 off%" 11 when brand "%rs12 off%" 12 when brand "%rs13 off%" 13 when brand "%rs14 off%" 14 when brand "%rs15 off%" 15 when brand "%rs16 off%" 16 when brand "%rs17 off%" 17 when brand "%rs18 off%" 18 when brand "%rs19 off%" 19 when brand "%rs20 off%" 20 when brand "%rs21 off%" 21 when brand "%rs22 off%" 22 when brand "%rs23 off%" 23 when brand "%rs24 off%" 24 when brand "%rs25 off%" 25 when brand "%rs26 off%" 26 when brand "%rs27 off%" 27 when brand "%rs28 off%" 28 when brand "%rs29 off%" 29 when brand "%rs30 off%" 30 when brand "%rs31 off%" 31 when brand "%rs32 off%" 32 when brand "%rs33 off%" 33 when brand "%rs34 off%" 34 when brand "%rs35 off%" 35 when brand "%rs36 off%" 36 when brand "%rs37 off%" 37 when brand "%rs38 off%" 38 when brand "%rs39 off%" 39 when brand "%rs40 off%" 40 when brand "%rs41 off%" 41 when brand "%rs42 off%" 42 when brand "%rs43 off%" 43 when brand "%rs44 off%" 44 when brand "%rs45 off%" 45 when brand "%rs46 off%" 46 when brand "%rs47 off%" 47 when brand "%rs48 off%" 48 when brand "%rs49 off%" 49 when brand "%rs50 off%" 50 when brand "%rs 1off%" 1 when brand "%rs 2off%" 2 when brand "%rs 3off%" 3 when brand "%rs 4off%" 4 when brand "%rs 5off%" 5 when brand "%rs 6off%" 6 when brand "%rs 7off%" 7 when brand "%rs 8off%" 8 when brand "%rs 9off%" 9 when brand "%rs 10off%" 10 when brand "%rs 11off%" 11 when brand "%rs 12off%" 12 when brand "%rs 13off%" 13 when brand "%rs 14off%" 14 when brand "%rs 15off%" 15 when brand "%rs 16off%" 16 when brand "%rs 17off%" 17 when brand "%rs 18off%" 18 when brand "%rs 19off%" 19 when brand "%rs 20off%" 20 when brand "%rs 21off%" 21 when brand "%rs 22off%" 22 when brand "%re 1 off%" 1 when brand "%re 2 off%" 2 when brand "%re 3 off%" 3 when brand "%re 4 off%" 4 when brand "%re 5 off%" 5 when brand "%re 6 off%" 6 when brand "%re 7 off%" 7 when brand "%re 8 off%" 8 when brand "%re 9 off%" 9 when brand "%re 10 off%" 10 when brand "%re 11 off%" 11 when brand "%re 12 off%" 12 when brand "%re 13 off%" 13 when brand "%re 14 off%" 14 when brand "%re 15 off%" 15 when brand "%re 16 off%" 16 when brand "%re 17 off%" 17 when brand "%re 18 off%" 18 when brand "%re 19 off%" 19 when brand "%re 20 off%" 20 when brand "%re 21 off%" 21 when brand "%re 22 off%" 22 when brand "%re 23 off%" 23 when brand "%re 24 off%" 24 when brand "%re 25 off%" 25 when brand "%re 26 off%" 26 when brand "%re 27 off%" 27 when brand "%re 28 off%" 28 when brand "%re 29 off%" 29 when brand "%re 30 off%" 30 when brand "%re 31 off%" 31 when brand "%re 32 off%" 32 when brand "%re 33 off%" 33 when brand "%re 34 off%" 34 when brand "%re 35 off%" 35 when brand "%re 36 off%" 36 when brand "%re 37 off%" 37 when brand "%re 38 off%" 38 when brand "%re 39 off%" 39 when brand "%re 40 off%" 40 when brand "%re 41 off%" 41 when brand "%re 42 off%" 42 when brand "%re 43 off%" 43 when brand "%re 44 off%" 44 when brand "%re 45 off%" 45 when brand "%re 46 off%" 46 when brand "%re 47 off%" 47 when brand "%re 48 off%" 48 when brand "%re 49 off%" 49 when brand "%re 50 off%" 50 when brand "%re 51 off%" 51 when brand "%re 52 off%" 52 when brand "%re 53 off%" 53 when brand "%re 54 off%" 54 when brand "%re 55 off when brand "%re1 off%" 1 when brand "%re2 off%" 2 when brand "%re3 off%" 3 when brand "%re4 off%" 4 when brand "%re5 off%" 5 when brand "%re6 off%" 6 when brand "%re7 off%" 7 when brand "%re8 off%" 8 when brand "%re9 off%" 9 when brand "%re10 off%" 10 when brand "%re11 off%" 11 when brand "%re12 off%" 12 when brand "%re13 off%" 13 when brand "%re14 off%" 14 when brand "%re15 off%" 15 when brand "%re16 off%" 16 when brand "%re17 off%" 17 when brand "%re18 off%" 18 when brand "%re19 off%" 19 when brand "%re20 off%" 20 when brand "%re21 off%" 21 when brand "%re22 off%" 22 when brand "%re23 off%" 23 when brand "%re24 off%" 24 when brand "%re25 off%" 25 when brand "%re26 off%" 26 when brand "%re27 off%" 27 when brand "%re28 off%" 28 when brand "%re29 off%" 29 when brand "%re30 off%" 30 when brand "%re31 off%" 31 when brand "%re32 off%" 32 when brand "%re33 off%" 33 when brand "%re34 off%" 34 when brand "%re35 off%" 35 when brand "%re36 off%" 36 when brand "%re37 off%" 37 when brand "%re38 off%" 38 when brand "%re39 off%" 39 when brand "%re40 off%" 40 when brand "%re41 off%" 41 when brand "%re42 off%" 42 when brand "%re43 off%" 43 when brand "%re44 off%" 44 when brand "%re45 off%" 45 when brand "%re46 off%" 46 when brand "%re47 off%" 47 when brand "%re48 off%" 48 when brand "%re49 off%" 49 when brand "%re50 off%" 50 when brand "%re51 off%" 51 when brand "%re52 off%" 52 when brand "%re53 off%" 53 when brand "%re54 off%" 54 when brand "%re55 off%" 55 when brand "%re56 off%" 56 when brand "%rs 1 off%" 1 when brand "%rs 1.50 off%" 1.5 when brand "%rs 2 off%" 2 when brand "%rs 2.50 off%" 2.5 when brand "%rs 3 off%" 3 when brand "%rs 3.50 off%" 3.5 when brand "%rs 4 off%" 4 when brand "%rs 4.50 off%" 4.5 when brand "%rs 5 off%" 5 when brand "%rs 5.50 off%" 5.5 when brand "%rs 6 off%" 6 when brand "%rs 6.50 off%" 6.5 when brand "%rs 7 off%" 7 when brand "%rs 7.50 off%" 7.5 when brand "%rs 8 off%" 8 when brand "%rs 8.50 off%" 8.5 when brand "%rs 9 off%" 9 when brand "%rs 9.50 off%" 9.5 when brand "%rs 10 off%" 10 when brand "%rs 10.50 off%" 10.5 when brand "%rs 10.5off%" 10.5 when brand "%rs 11 off%" 11 when brand "%rs 11.50 off%" 11.5 when brand "%rs 12 off%" 12 when brand "%rs 12.50 off%" 12.5 when brand "%rs 13 off%" 13 when brand "%rs 13.50 off%" 13.5 when brand "%rs 14 off%" 14 when brand "%rs 14.50 off%" 14.5 when brand "%rs 15 off%" 15 when brand "%rs 15.50 off%" 15.5 when brand "%rs 16 off%" 16 when brand "%rs 16.50 off%" 16.5 when brand "%rs 17 off%" 17 when brand "%rs 17.50 off%" 17.5 when brand "%rs 18 off%" 18 when brand "%rs 18.50 off%" 18.5 when brand "%rs 19 off%" 19 when brand "%rs 19.50 off%" 19.5 when brand "%rs 20 off%" 20 when brand "%rs 20.50 off%" 20.5 when brand "%rs 21 off%" 21 when brand "%rs 21.50 off%" 21.5 when brand "%rs 22 off%" 22 when brand "%rs 22.50 off%" 22.5 when brand "%rs 23 off%" 23 when brand "%rs 23.50 off%" 23.5 when brand "%rs 24 off%" 24 when brand "%rs 24.50 off%" 24.5 when brand "%rs 25 off%" 25 when brand "%rs 25.50 off%" 25.5 when brand "%rs 26 off%" 26 when brand "%rs 26.50 off%" 26.5 when brand "%rs 27 off%" 27 when brand "%rs 27.50 off%" 27.5 when brand "%rs 28 off%" 28 when brand "%rs 28.50 off%" 28.5 when brand "%rs 29 off%" 29 when brand "%rs 29.50 off%" 29.5 when brand "%rs 30 off%" 30 when brand "%rs 30.50 off%" 30.5 when brand "%rs 31 off%" 31 when brand "%rs 31.50 off%" 31.5 when brand "%rs 32 off%" 32 when brand "%rs 32.50 off%" 32.5 when brand "%rs 33 off%" 33 when brand "%rs 33.50 off%" 33.5 when brand "%rs 34 off%" 34 when brand "%rs 34.50 off%" 34.5 when brand "%rs 35 off%" 35 when brand "%rs 35.50 off%" 35.5 when brand "%rs 36 off%" 36 when brand "%rs 36.50 off%" 36.5 when brand "%rs 37 off%" 37 when brand "%rs 37.50 off%" 37.5 when brand "%rs 38 off%" 38 when brand "%rs 38.50 off%" 38.5 when brand "%rs 39 off%" 39 when brand "%rs 39.50 off%" 39.5 when brand "%rs 40 off%" 40 when brand "%rs 40.50 off%" 40.5 when brand "%rs 41 off%" 41 when brand "%rs 41.50 off%" 41.5 when brand "%rs 42 off%" 42 when brand "%rs 42.50 off%" 42.5 when brand "%rs 43 off%" 43 when brand "%rs 43.50 off%" 43.5 when brand "%rs 44 off%" 44 when brand "%rs 44.50 off%" 44.5 when brand "%rs 45 off%" 45 when brand "%rs 45.50 off%" 45.5 when brand "%rs 46 off%" 46 when brand "%rs 46.50 off%" 46.5 when brand "%rs 47 off%" 47 when brand "%rs 47.50 off%" 47.5 when brand "%rs 48 off%" 48 when brand "%rs 48.50 off%" 48.5 when brand "%rs 49 off%" 49 when brand "%rs 49.50 off%" 49.5 when brand "%rs 50 off%" 50 when brand "%rs 50.50 off%" 50.5 when brand "%rs.1 off%" 1 when brand "%rs.2 off%" 2 when brand "%rs.3 off%" 3 when brand "%rs.4 off%" 4 when brand "%rs.5 off%" 5 when brand "%rs.6 off%" 6 when brand "%rs.7 off%" 7 when brand "%rs.8 off%" 8 when brand "%rs.9 off%" 9 when brand "%rs.10 off%" 10 when brand "%rs.11 off%" 11 when brand "%rs.12 off%" 12 when brand "%rs.13 off%" 13 when brand "%rs.14 off%" 14 when brand "%rs.15 off%" 15 when brand "%rs.16 off%" 16 when brand "%rs.17 off%" 17 when brand "%rs.18 off%" 18 when brand "%rs.19 off%" 19 when brand "%rs.20 off%" 20 when brand "%rs.21 off%" 21 when brand "%rs.22 off%" 22 when brand "%rs.23 off%" 23 when brand "%rs.24 off%" 24 when brand "%rs.25 off%" 25 when brand "%rs.26 off%" 26 when brand "%rs.27 off%" 27 when brand "%rs.28 off%" 28 when brand "%rs.29 off%" 29 when brand "%rs.30 off%" 30 when brand "%rs.31 off%" 31 when brand "%rs.32 off%" 32 when brand "%rs.33 off%" 33 when brand "%rs.34 off%" 34 when brand "%rs.35 off%" 35 when brand "%rs.36 off%" 36 when brand "%rs.37 off%" 37 when brand "%rs.38 off%" 38 when brand "%rs.39 off%" 39 when brand "%rs.40 off%" 40 when brand "%rs.41 off%" 41 when brand "%rs.42 off%" 42 when brand "%rs.43 off%" 43 when brand "%rs.44 off%" 44 when brand "%rs.45 off%" 45 when brand "%rs.46 off%" 46 when brand "%rs.47 off%" 47 when brand "%rs.48 off%" 48 when brand "%rs.49 off%" 49 when brand "%rs.50 off%" 50 when brand "%rs. 1 off%" 1 when brand "%rs. 2 off%" 2 when brand "%rs. 3 off%" 3 when brand "%rs. 4 off%" 4 when brand "%rs. 5 off%" 5 when brand "%rs. 6 off%" 6 when brand "%rs. 7 off%" 7 when brand "%rs. 8 off%" 8 when brand "%rs. 9 off%" 9 when brand "%rs. 10 off%" 10 when brand "%rs. 11 off%" 11 when brand "%rs. 12 off%" 12 when brand "%rs. 13 off%" 13 when brand "%rs. 14 off%" 14 when brand "%rs. 15 off%" 15 when brand "%rs. 16 off%" 16 when brand "%rs. 17 off%" 17 when brand "%rs. 18 off%" 18 when brand "%rs. 19 off%" 19 when brand "%rs. 20 off%" 20 when brand "%rs. 21 off%" 21 when brand "%rs. 22 off%" 22 when brand "%rs. 23 off%" 23 when brand "%rs. 24 off%" 24 when brand "%rs. 25 off%" 25 when brand "%rs. 26 off%" 26 when brand "%rs. 27 off%" 27 when brand "%rs. 28 off%" 28 when brand "%rs. 29 off%" 29 when brand "%rs. 30 off%" 30 when brand "%rs. 31 off%" 31 when brand "%rs. 32 off%" 32 when brand "%rs. 33 off%" 33 when brand "%rs. 34 off%" 34 when brand "%rs. 35 off%" 35 when brand "%rs. 36 off%" 36 when brand "%rs. 37 off%" 37 when brand "%rs. 38 off%" 38 when brand "%rs. 39 off%" 39 when brand "%rs. 40 off%" 40 when brand "%rs. 41 off%" 41 when brand "%rs. 42 off%" 42 when brand "%rs. 43 off%" 43 when brand "%rs. 44 off%" 44 when brand "%rs. 45 off%" 45 when brand "%rs. 46 off%" 46 when brand "%rs. 47 off%" 47 when brand "%rs. 48 off%" 48 when brand "%rs. 49 off%" 49 when brand "%rs. 50 off%" 50 when brand "%rs.1/-off%" 1 when brand "%rs.2/-off%" 2 when brand "%rs.3/-off%" 3 when brand "%rs.4/-off%" 4 when brand "%rs.5/-off%" 5 when brand "%rs.6/-off%" 6 when brand "%rs.7/-off%" 7 when brand "%rs.8/-off%" 8 when brand "%rs.9/-off%" 9 when brand "%rs.10/-off%" 10 when brand "%rs.11/-off%" 11 when brand "%rs.12/-off%" 12 when brand "%rs.13/-off%" 13 when brand "%rs.14/-off%" 14 when brand "%rs.15/-off%" 15 when brand "%rs.16/-off%" 16 when brand "%rs.17/-off%" 17 when brand "%rs.18/-off%" 18 when brand "%rs.19/-off%" 19 when brand "%rs.20/-off%" 20 when brand "%rs.21/-off%" 21 when brand "%rs.22/-off%" 22 when brand "%rs.23/-off%" 23 when brand "%rs.24/-off%" 24 when brand "%rs.25/-off%" 25 when brand "%rs.26/-off%" 26 when brand "%rs.27/-off%" 27 when brand "%rs.28/-off%" 28 when brand "%rs.29/-off%" 29 when brand "%rs.30/-off%" 30 when brand "%rs.31/-off%" 31 when brand "%rs.32/-off%" 32 when brand "%rs.33/-off%" 33 when brand "%rs.34/-off%" 34 when brand "%rs.35/-off%" 35 when brand "%rs.36/-off%" 36 when brand "%rs.37/-off%" 37 when brand "%rs.38/-off%" 38 when brand "%rs.39/-off%" 39 when brand "%rs.40/-off%" 40 when brand "%rs.41/-off%" 41 when brand "%rs.42/-off%" 42 when brand "%rs.43/-off%" 43 when brand "%rs.44/-off%" 44 when brand "%rs.45/-off%" 45 when brand "%rs.46/-off%" 46 when brand "%rs.47/-off%" 47 when brand "%rs.48/-off%" 48 when brand "%rs.49/-off%" 49 when brand "%rs.50/-off%" 50 when brand "%rs. 1/-off%" 1 when brand "%rs. 2/-off%" 2 when brand "%rs. 3/-off%" 3 when brand "%rs. 4/-off%" 4 when brand "%rs. 5/-off%" 5 when brand "%rs. 6/-off%" 6 when brand "%rs. 7/-off%" 7 when brand "%rs. 8/-off%" 8 when brand "%rs. 9/-off%" 9 when brand "%rs. 10/-off%" 10 when brand "%rs. 11/-off%" 11 when brand "%rs. 12/-off%" 12 when brand "%rs. 13/-off%" 13 when brand "%rs. 14/-off%" 14 when brand "%rs. 15/-off%" 15 when brand "%rs. 16/-off%" 16 when brand "%rs. 17/-off%" 17 when brand "%rs. 18/-off%" 18 when brand "%rs. 19/-off%" 19 when brand "%rs. 20/-off%" 20 when brand "%rs. 21/-off%" 21 when brand "%rs. 22/-off%" 22 when brand "%rs. 23/-off%" 23 when brand "%rs. 24/-off%" 24 when brand "%rs. 25/-off%" 25 when brand "%rs. 26/-off%" 26 when brand "%rs. 27/-off%" 27 when brand "%rs. 28/-off%" 28 when brand "%rs. 29/-off%" 29 when brand "%rs. 30/-off%" 30 when brand "%rs. 31/-off%" 31 when brand "%rs. 32/-off%" 32 when brand "%rs. 33/-off%" 33 when brand "%rs. 34/-off%" 34 when brand "%rs. 35/-off%" 35 when brand "%rs. 36/-off%" 36 when brand "%rs. 37/-off%" 37 when brand "%rs. 38/-off%" 38 when brand "%rs. 39/-off%" 39 when brand "%rs. 40/-off%" 40 when brand "%rs. 41/-off%" 41 when brand "%rs. 42/-off%" 42 when brand "%rs. 43/-off%" 43 when brand "%rs. 44/-off%" 44 when brand "%rs. 45/-off%" 45 when brand "%rs. 46/-off%" 46 when brand "%rs. 47/-off%" 47 when brand "%rs. 48/-off%" 48 when brand "%rs. 49/-off%" 49 when brand "%rs. 50/-off%" 50 when brand "%rs. 1/- off%" 1 when brand "%rs. 2/- off%" 2 when brand "%rs. 3/- off%" 3 when brand "%rs. 4/- off%" 4 when brand "%rs. 5/- off%" 5 when brand "%rs. 6/- off%" 6 when brand "%rs. 7/- off%" 7 when brand "%rs. 8/- off%" 8 when brand "%rs. 9/- off%" 9 when brand "%rs. 10/- off%" 10 when brand "%rs. 11/- off%" 11 when brand "%rs. 12/- off%" 12 when brand "%rs. 13/- off%" 13 when brand "%rs. 14/- off%" 14 when brand "%rs. 15/- off%" 15 when brand "%rs. 16/- off%" 16 when brand "%rs. 17/- off%" 17 when brand "%rs. 18/- off%" 18 when brand "%rs. 19/- off%" 19 when brand "%rs. 20/- off%" 20 when brand "%rs. 21/- off%" 21 when brand "%rs. 22/- off%" 22 when brand "%rs. 23/- off%" 23 when brand "%rs. 24/- off%" 24 when brand "%rs. 25/- off%" 25 when brand "%rs. 26/- off%" 26 when brand "%save rs.1%" 1 when brand "%save rs.2%" 2 end );
according syntax highlighting in stack overflow, following line:
when brand "%re 55 off is missing terminating quote. should be:
when brand "%re 55 off" 55 <-- guess value also, single quotes preferred on double quotes (for instance, single quotes sql standard string constants).
edit:
if i'm correct in want do, might want consider construct instead:
substring_index(substring_index(brand, ' off', 1), ' ', -1) + 0 this may able replace entire case statement (depends on data looks like, might work).
Comments
Post a Comment