using string values for between operator in mysql

Question

I have a table sales having attributes as salesId,salesDate,..etc. The salesId column is a varchar.I need to concat the year value of the salesDate to the salesId based on a condition of the month of the salesDate. I need to do this for a range of around 100 salesID. Eg: old salesId = 7 and corresponding salesDate = '2018-05-07' then new required salesId = '7/2018-2019' So i tried the following :

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(salesId as unsigned) between "7" and "10";

However, i get the following error:

Error Code: 1292. Truncated incorrect INTEGER value: '1/17-18'

I even tried without using any Cast() as

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where salesId between "7" and "10";

but in this case the query runs fine but i get:

0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0

I can't figure out the error or how to proceed.Could someone please provide some guidance in this ? Thanks. Sample data

salesId salesDate
7          2017-05-15
8          2017-06-16
9          2017-07-18
10         2017-08-20

...

Required Result

 salesId                  salesDate
    7/2017-2018         2017-05-15
    8/2017-2018         2017-06-16
    9/2017-2018         2017-07-18
    10/2016-2017        2017-02-20

Please provide sample data and expected results. It is not that clear what you are trying to accomplish here. — GMB, Apr 04 '20 at 18:03

nbk · Answer 1 · 2020-04-04T21:17:04.690

0

The field salesId must be text like

You have to change your Update query to include if thecolumn where already update, so that it takes with SUBSTRING_INDEX only the number

 update sales
set salesId = case
                when month(salesDate)>=4 then concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(SUBSTRING_INDEX(salesId,'/',1) as unsigned) between "7" and "10";

so that your update function can work

CREATE TABLE sales (
  `salesId` VARCHAR(20),
  `salesDate` VARCHAR(10)
);

INSERT INTO sales
  (`salesId`, `salesDate`)
VALUES
  ('7', '2017-05-15'),
  ('8', '2017-06-16'),
  ('9', '2017-07-18'),
  ('10', '2017-08-20');

✓

✓

    update sales
set salesId = case
                when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(salesId as unsigned) between "7" and "10";

✓

SELECT *  from sales

salesId      | salesDate 
:----------- | :---------
7/2017-2018  | 2017-05-15
8/2017-2018  | 2017-06-16
9/2017-2018  | 2017-07-18
10/2017-2018 | 2017-08-20

 update sales
set salesId = case
                when month(salesDate)>=4 then concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate),"-",year(salesDate)+1))
                else
                    concat(SUBSTRING_INDEX(salesId,'/',1),concat("/",year(salesDate)-1,"-",year(salesDate)))
                end
where cast(SUBSTRING_INDEX(salesId,'/',1) as unsigned) between "7" and "10";

✓

SELECT *  from sales

salesId      | salesDate 
:----------- | :---------
7/2017-2018  | 2017-05-15
8/2017-2018  | 2017-06-16
9/2017-2018  | 2017-07-18
10/2017-2018 | 2017-08-20

db<>fiddle here

edited Apr 04 '20 at 21:17

answered Apr 04 '20 at 18:15

nbk

45,398
8
30
47

yes the field salesId is already a varchar. I tired your above snippet but i still get the error: `Error Code: 1292. Truncated incorrect INTEGER value: '1/17-18' ` – pranay Apr 04 '20 at 18:26
please show me your create table or make a fiddle to demonstrate your problem, as far as i can read your error message it shows your salesId is integer not Varchar in the right size – nbk Apr 04 '20 at 18:39
the salesID is varchar(100) however the salesDate is of type date – pranay Apr 04 '20 at 18:58
however this also gives correct results https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=acab94a38ba7470c4eaea92aba7cf1ea but not in my db – pranay Apr 04 '20 at 19:11
your error code shows 1/17-18 which would not be affected in your update query, try it with your actual table on sql fiddle and see if you can reproduce it, you could also see if your mysql version can be updated, sometimes it is simpky buggy – nbk Apr 04 '20 at 19:15
mysql version is 8.0.13..Moreover, the update query is working fine if i update for individual records, i.e without using the between operator. – pranay Apr 04 '20 at 19:26
i.e `update sales set salesId = case when month(salesDate)>=4 then concat(salesId,concat("/",year(salesDate),"-",year(salesDate)+1)) else concat(salesId,concat("/",year(salesDate)-1,"-",year(salesDate))) end where salesId ="10"; ` – pranay Apr 04 '20 at 19:34
Ok i get it i changed my answer, so that your error doesn't occur, Please add next time your complete data, because your data doesn't show, that the ids arent ids at all, at least not that simople – nbk Apr 04 '20 at 21:18

score 0 · Accepted Answer · answered Apr 04 '20 at 19:56

The error which i was getting earlier was actually a warning as stated here. So after using update ignore i was able to execute the query as get expected results. However i would be waiting for a more comprehensive answer as to get it working without the need to ignore. Thanks

using string values for between operator in mysql

2 Answers2