Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...This was the ONLY place that I could find information that I could use to resolve the problem. So thanks once again to member TomSark and the SQL forum!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > MySQL Forum

Using SQL to update value on some column

thread436-1648601
Read More Threads Like This One
explorer1979 (IS/IT--Management)
16 May 11 11:30
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 (Vendor)
16 May 11 13:41

CODE

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 (TechnicalUser)
16 May 11 21:59
close, daddy, but no cigar

mysql does not use the plus sign for string concatenation

CODE

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

 

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

explorer1979 (IS/IT--Management)
17 May 11 4:49
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 (IS/IT--Management)
17 May 11 4:54
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 (TechnicalUser)
17 May 11 6:53
boy, you really are new to sql, aren't you

CODE

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

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

whosrdaddy (Vendor)
17 May 11 7:05

Quote:


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 (IS/IT--Management)
18 May 11 3:16
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
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To MySQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close