Using SQL to update value on some column

[explorer1979]

Hi all,

I have some value in the Database that need to update, but I don't know how to write the SQL code.

MySQL Database named "abc"
Have a table named "products" in the above database "abc"
In the table 'products" have "products_id", products_model", "manufacturers_id" three column

I want to change some value in the "products_model" that base on manufacturers_id's value is 11


For example of the value in products_model are list below

000160
000175
002358
002419
00185600
00589000

My problems is how to using SQL to update the above value on products_model with manufacturers_id=11 that the format I want below.


000160 --> EK000160
000175 --> EK000175
002358 --> EK002358
002419 --> EK002419
00185600 --> EK00185600
00589000 --> EK00589000

I am new on using SQL to update data, but I hope can do like that.

Anyone can tell me how to write the SQL for above case?

Thank you very much of your time and kindly help.

 

[whosrdaddy]

UPDATE products
 SET product_model = 'EK' + product_model
 WHERE manufacturers_id=11

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[r937]

close, daddy, but no cigar

mysql does not use the plus sign for string concatenation

UPDATE products 
   SET product_model = CONCAT('EK',product_model)
 WHERE manufacturers_id = 11

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[explorer1979]

Hi Daddy,

First, thank you very much.
But I run your SQL, it not work, it will make the result like that

000160 --> 160
000175 --> 175
002358 --> 2358

 

[explorer1979]

Hi r937,

Thank you very very much,

Your way work for me....

But not I just discovered one problems.... it is not by your SQL, it is by my other user.

Since some user manual help change the 000160 to EK000160...

mean that on the database also have some value are using the EK already.....

so your SQL will make the result like that on the value that who the user manual changed before like that

EK000160 --> EKEK000160
EK000175 --> EKEK000175
EK002358 --> EKEK002358

etc...

Do it possible can make the SQL check have the EK value or something like that before update the old value? So that if some value have EK000160 like above will keep not to change.


Thank you very much of your time and help.

 

[r937]

boy, you really are new to sql, aren't you

UPDATE products
   SET product_model = CONCAT('EK',product_model)
 WHERE manufacturers_id = 11
   [blue]AND product_model NOT LIKE 'EK%'[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[whosrdaddy]

close, daddy, but no cigar

mysql does not use the plus sign for string concatenation

argh, it's been a while.
playing too much with mssql

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[explorer1979]

Hi Rudy,

Yes, I am really new to SQL, so just can post here ask the expert like you and other.

Thank you very very much, it work for me. And you save my life. Thank you.

Best Regards,
Jimmy Chan