CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user3310883
user3310883

Reputation: 95

How to cast varchar into int properly?

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

Upvotes: 0

Views: 581

Answers (4)

user3322997
user3322997

Reputation: 

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

Upvotes: 0

SAT
SAT

Reputation: 645

Try this code

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

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

i guess you making wrong in the ON clause

try this

    ON Towar.Tow_id = TowarZamowienie.TowZam_id

DEMO

Upvotes: 1

Ben
Ben

Reputation: 401

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

Upvotes: 0

Related Questions