How to cast varchar into int properly?

Question

I've prepared sql query like this

UPDATE Towar
JOIN TowarZamowienie ON Towar.Tow_id = TowarZamowienie.Tow_id
SET Tow_ilosc = Tow_ilosc - CAST(TowZam_ilosc AS UNSIGNED);

and it returns me Modified records: 0. I will admit that I've got records in database

Here are my tables:

TowarZamowienie 

1   TowZam_id         int(19) AUTO_INCREMENT   
2   Tow_id            int(255)            
3   Zam_id            int(255)             
4   TowZam_ilosc      varchar(10)

Towar

1     Tow_id           int(255)
2     Tow_ilosc        int(6)

Here is my schema http://sqlfiddle.com/#!2/981b4/1

Check this out... Possible duplicate... http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql — chris_techno25, Feb 18 '14 at 11:22
@chris_techno25 it is not duplicate becouse i've got no error there. — user3310883, Feb 18 '14 at 11:25
Ahhh I see, well then echo_Me is right. It will definitely be easier if you show us an example data :) — chris_techno25, Feb 18 '14 at 11:26
I know i have got 0os but it should be work for 1 am I right? — user3310883, Feb 18 '14 at 11:34
@user3310883 I checked sqlfiddle. I used Select * from Towar and Select * from TowarZamowienie. The first row that pops out, you'd want Tow_ilosc to be equal to 100 right? Coz 100-0=100. Did I get that right? — chris_techno25, Feb 18 '14 at 12:12
look at this, how does it works now. look at this i59.tinypic.com/mif3fa.png it should change values where tow_id is the same as tow_id from towarzamowienie i60.tinypic.com/2i7sdu8.png It is wrong — user3310883, Feb 18 '14 at 12:14

score 1 · Accepted Answer · answered Feb 18 '14 at 11:41

1

i guess you making wrong in the ON clause

try this

    ON Towar.Tow_id = TowarZamowienie.TowZam_id

DEMO

answered Feb 18 '14 at 11:41

echo_Me

37,078
5
58
78

look at this http://i59.tinypic.com/mif3fa.png it should change values where tow_id is the same as tow_id from towarzamowienie http://i60.tinypic.com/2i7sdu8.png – user3310883 Feb 18 '14 at 11:54
@user3310883 i have seen the images but you have Twozam_ilosc is 0 according to two_zam 5 , how will this be 99 ? look this [demo](http://sqlfiddle.com/#!2/8596ce/1) – echo_Me Feb 18 '14 at 12:24
In this case it should return 100-0 =100, no change – user3310883 Feb 18 '14 at 12:30
but when TowZam_ilosc is for example 1 it should return 99 in Towar.Tow_ilosc – user3310883 Feb 18 '14 at 12:31
I don't get why Towar with Tow_id 16 and more has changed.. The only one Towar modified in this case should be Towar with Tow_id 5 – user3310883 Feb 18 '14 at 12:35
dude i really dont understand you , look this [demo](http://sqlfiddle.com/#!2/e61702/1) i changed just the SET .... with -1 . your query is good , you didnt get twoyam 5 to change because its equivalent value of twozam_ilosc is 0 . – echo_Me Feb 18 '14 at 12:53

SAT · Answer 2 · 2014-02-18T11:46:19.293

0

Try this code

  UPDATE Towar
 JOIN 
    TowarZamowienie ON Towar.Tow_id = TowarZamowienie.TowZam_id
 SET 
    Tow_ilosc = Tow_ilosc - CAST(TowZam_ilosc AS UNSIGNED);

edited Feb 18 '14 at 11:46

answered Feb 18 '14 at 11:29

SAT

647
1
12
23

value have to be casted from string to int firstly – user3310883 Feb 18 '14 at 11:33

score 0 · Answer 3 · answered Feb 18 '14 at 11:35

Why not "cast" the Integer to Varchar? What if the Varchar has really 10 chars and the Integer will expect just 6 numbers? Try this:

CAST(Tow_ilosc as CHAR(10))

or

CONVERT(Tow_ilosc, CHAR(10))

or just use like in the where clause

WHERE Tow_ilosc LIKE TowZam_ilosc

(I don't like like)^^

score 0 · Answer 4 · edited Feb 18 '14 at 13:47

0

UPDATE Towar, TowarZamowienie
SET Towar.Tow_ilosc = Towar.Tow_ilosc - CAST(TowarZamowienie.TowZam_ilosc AS UNSIGNED)
WHERE Towar.Tow_id = TowarZamowienie.TowZam_id;

--OR

UPDATE Towar
JOIN TowarZamowienie ON Towar.Tow_id = TowarZamowienie.TowZam_id
SET Tow_ilosc = Tow_ilosc - CAST(TowZam_ilosc AS UNSIGNED);

edited Feb 18 '14 at 13:47

Bandreid

2,727
28
47

answered Feb 18 '14 at 13:25

Try using code formatting for the samples you post. Makes it easier to read. Thanks :) – Bandreid Feb 18 '14 at 13:46

How to cast varchar into int properly?

4 Answers4