how to use mysql update query and mysql insert query together?

Question

this the condition: there is a form in html and php haivng around 120 fields its for a website i am making this form on submitting goes to a php page where i first retrive all the values using $_REQUEST[]and then using insert query insert all of them in their specific coloums in the same table in my mysql database. Now i will have to do all the process again for updating these values. Becuase syntax for insert query and update query are quite different .

I dont want to write another 100 lines of code . Is there any way to use the code i wrote inside my insert query to use to update the data.?

Jay · Accepted Answer · 2011-07-26 18:20:03Z

6

Actually in MySQL there is an alternative syntax for insert that is very similar to the syntax for update. You can write

insert customer set customerid=12345, firstname='Fred', lastname='Jones

etc.

Personally I prefer this syntax because it's easy to see what value is going into each field. This is especially true on records with long lists of fields.

On the minus side, it's not standard SQL, so if you ever decide to port your app to a different database engine, all your inserts would have to be rewritten.

Another option I've occasionally used is to write a little function to create your insert and update statements. Then the syntax of your function can be the same, no matter how different the generated code is.

answered Jul 26, 2011 at 18:20

Jay

27.6k12 gold badges69 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dannysood Over a year ago

i used the standard syntax this time but for the future i will definately use this syntax insert and update are quite easy that way

Parris Varney Over a year ago

How have I never known about this syntax?

Jay Over a year ago

@pmv: Because you have lived a deprived, impoverished life.

dannysood · Accepted Answer · 2011-07-26 19:41:16Z

2

Another alternative, and depending on requirements and keys, you could use:

replace into tbl (<cols>) values (<vals>)

which will insert if not exist, or replace based on keys (insert/update in one query)

or if you are only inserting and don't want to insert twice, you could use:

insert ignore into tbl (<cols>) values (<vals>)

where if the record is already inserted based on keys, it is gracefully ignored

for more info http://dev.mysql.com/doc/refman/5.0/en/replace.html

edited Jul 26, 2011 at 19:41

dannysood

1,1493 gold badges18 silver badges36 bronze badges

answered Jul 26, 2011 at 18:34

Adam MacDonald

1,95815 silver badges19 bronze badges

4 Comments

dannysood Over a year ago

yes this is exactly what i was looking for but i couldnt understand the difference between the two (my primary key is names of different cities and what i exactly want is that if the name of city already exists in the database it would update the data for the city else add a new row) which one of the two would be better in this scenario

Adam MacDonald Over a year ago

Hi Danny, for your request, you would want to use replace into. Remember, if your cities include more than one state, to avoid city name conflicts (same city name in multiple states), you may want to make state and city a unique key .. that way, when you replace into, it will use both the city and state, and not update a city in a different state

dannysood Over a year ago

thanks and yes i have already made both state and city primary. Thanks :)

Adam MacDonald Over a year ago

Hi Danny, yw :) .. this is just an option/suggestion, city and state should be indexes, and (city+state) together should be unique .. ALTER TABLE <tbl> ADD UNIQUE (city ,state); .. or with phpMyAdmin, put a checkmark into both city and state, and then click on Unique (U), perhaps an auto increment id or something could be primary

wonk0 · Accepted Answer · 2011-07-26 18:15:20Z

1

There is a quite similar syntax for INSERT and UPDATE:

INSERT INTO <table> SET
 column1 = value1,
 column2 = value2,
 ...
;

UPDATE <table> SET
 column1 = value1,
 column2 = value2,
 ...
WHERE <condition>
;

answered Jul 26, 2011 at 18:15

wonk0

14.1k1 gold badge23 silver badges15 bronze badges

1 Comment

dannysood Over a year ago

i user the diiferent syntax( "insert into table(coloum1,coloumm2,......)value('value1','value2'........)

Marc B · Accepted Answer · 2011-07-26 18:16:28Z

1

INSERT INTO yourtable (field1, field2, field3, ...)
VALUES ($field1, $field2, $field3, ...)
ON DUPLICATE KEY UPDATE field1=VALUES(field1), field2=VALUES(field2), etc...

Details on this construct here.

answered Jul 26, 2011 at 18:16

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

how to use mysql update query and mysql insert query together?

4 Answers 4

3 Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related