0

I got a problem on updating column from another column in same table. I know this question is duplicate. But here is the situation. I have this table structure:

  login   |   card_id   | card_serial
--------------------------------------
  A1149     1446700898    NULL
  A672      1446845746    NULL
  A626      1446581746    NULL

Now, I want to update card_serial column with hex value using hex function. The data supposed to be like this:

  login   |   card_id   | card_serial
--------------------------------------
  A1149     1446700898    62E73A56
  A672      1446845746    321D3D56
  A626      1446581746    F2153956

But when I try to update card_serial column with my SQL statement, it always return value 36343431.

This is my statement:

UPDATE card_id_without_serial a 
JOIN card_id_without_serial b ON b.login = a.login
SET a.card_serial = concat(substring(hex(b.card_id), 7,2), substring(hex(b.card_id), 5,2), substring(hex(b.card_id), 3,2), substring(hex(b.card_id), 1,2));

Please help me and thank you

softboxkid
  • 877
  • 6
  • 13
  • 31
  • Possible duplicate of [mysql update column with value from another table](http://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table) – Huey Nov 23 '15 at 01:49
  • 2
    Why are you using a join? You can just update your column with a regular update. `UPDATE table_name SET col3 = HEX(col2)`; – chrisShick Nov 23 '15 at 01:50
  • Hi chrisShick, I already try your suggestion, but it still return value 36343431. That's y i try to join the table. But the problem still not solve – softboxkid Nov 23 '15 at 02:02
  • No, chrisShick's answer is correct. You must have done the update incorrectly. – Ray Baxter Nov 23 '15 at 04:36

0 Answers0