I need help with a MySQL query.

Question

Ok I have a table in my mysql query browser like shown below:

NAME:         Jobs:

Kate          Contractor
John          Janitor
Bob           Writer
Don           Waitress

Let's say I want replace the job of Kate to artist. how would I do this as a MySQL Query. I know it involves the INSERT INTO thingy, but I'm not really sure.

Answer 1

Take a look at the mysql update syntax.

Answer 2

To do a replacement you need to uniquely identify the database row you want to replace. Usually there is an ID column to do that. Does your table have one?

You use the UPDATE thingy, not the INSERT INTO thingy, to change existing data.

If there is only one Kate in the database you can do something like this:

UPDATE my_table_name SET Jobs='Artist' WHERE NAME='Kate'

You want to replace NAME='Kate' with ID=999 if you have an ID column (where 999 needs to be replaced with Kate's actual ID), otherwise everyone named Kate will be turned into an artist.

EDIT:

Ricebowl has a good point if you let users enter this directly free-form. They can put in characters that have special meaning to SQL and do very bad things with your database. Get the basics down first, then spend some time reading about how to protect from "SQL Injection" attacks.

This whole thing is illustrated with one of my favorite comics:

(And see the source blog for more discussion)

Answer 3

UPDATE table_name SET Jobs="job_type" WHERE name="Kate";

Bearing in mind that this isn't properly parameterised for use in a web-application, nor in any way proofed against malicious use should it be exposed to the web.

Nor is 'Kate' a unique identifier, so there should be a primary key (of whatever type) used to identify a specific 'Kate' from your table, at which point the query is modified to:

UPDATE table_name SET Jobs="job_type" WHERE primary_key_name="unique_identifier_for_user";

Answer 4

UPDATE jobs_table SET `Jobs` = 'Artist' WHERE `name` = 'Kate'

Answer 5

UPDATE 'JobsTable' SET 'Jobs' = "Artist" WHERE 'Name' = "Kate"

Answer 6

Another way, don't foret about this feature

`REPLACE INTO jobs_table  (`Jobs`, `name`) ('Artist', 'Kate')`

When you don't know if the primary key 'Kate' exists or not you don't have to write

$a = mysql_query("select * where jobs = 'Kate'")
if($a)
    update
else
    insert

it gets faster and simplier!