Mysql Php Update Multiple Columns In Mysql

It is possible, and fairly easy, to update multiple rows of a MySQL table at the same time, with different values for each row. Unfortunately it’s not as easy as inserting, but once you see what’s []. Mysql> update table set last_update=now() where id=1; mysql> update table set last_monitor = last_update where id=1; anyway I think that mysql is clever enough to ask for now() only once per query. I tried the method u suggested, but with that, only the first of the 3 columns gets updated. I tries using 3 diff commands, and this, somehow, causes col 1 and 3 to update.

Active10 months ago

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

For instance, three updates into 1 query:

I read an example, but I really don't understand how to make the query. i.e:

I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition.any ideas?

Adriaan Koster

MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Delete Data MySQL Update Data MySQL Limit Data PHP - XML. Update Data In a MySQL. In this tutorial, create 1 file 1. Update_multiple.php Steps 1. Create table 'test_mysql' in database 'test'. Create file update_multiple.php.

13.2k3 gold badges34 silver badges50 bronze badges

Mysql Php Update Multiple Rows

4,33915 gold badges48 silver badges87 bronze badges

4 Answers

You can do it this way:

I don't understand your date format. Dates should be stored in the database using native date and time types.

Gordon LinoffGordon Linoff
854k38 gold badges356 silver badges454 bronze badges

MySQL allows a more readable way to combine multiple updates into a single query. This seems to better fit the scenario you describe, is much easier to read, and avoids those difficult-to-untangle multiple conditions.

This assumes that the user_rol, cod_office combination is a primary key. If only one of these is the PK, then add the other field to the UPDATE list.If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.

However, this approach makes prepared statements easier to build and more concise.

Adriaan Koster
13.2k3 gold badges34 silver badges50 bronze badges
2,6311 gold badge13 silver badges13 bronze badges

You can use a CASE statement to handle multiple if/then scenarios:

Hart COHart CO
29.5k5 gold badges35 silver badges49 bronze badges
Matteo Baldi
3,4589 gold badges24 silver badges41 bronze badges
Akshay BhanAkshay Bhan

protected by Nilesh RathodApr 19 '18 at 11:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged mysqlsqlsql-update or ask your own question.

Active8 years, 4 months ago

I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table:

I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated:

However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code:

The 'select query' in the above looks like this:

Is there anyway for the rows returned from the select to be put into a form that they can be used to update multiple fields?Thanks.

1,7358 gold badges42 silver badges75 bronze badges

4 Answers

57.5k8 gold badges81 silver badges128 bronze badges

You can do something similar to this:

Epson Aculaser M2000 has the capacity to print your documents and files as fast as possible as quickly as 28 pages per minutes. The printer machine also offers speed, quality, and efficiency for the office and residential people use. This fast and reasonably cost printer has the capacity to process your documents and files very rapidly. Epson aculaser m2000 driver windows 7 32bit. Additionally, it features flexible dual-sided documents and prints any documents and files at a very economical cost. The printing device delivers your documents on multiple paper size very quickly.

I found this related question on Stackoverflow on how to transform columns to rows:

Each point is surrounded by a square, although like the 350D / XT, a red dot illuminates in the middle when active. While the illuminated square outlines of the 30D look smarter, the nine-point AF system behind it remains the same and is a decent upgrade for the 400D / XTi from the earlier seven-point system. Interestingly Canon’s slightly altered the information running along the bottom of the viewfinder frame. Canon eos digital rebel xti. As before you have the choice of three AF modes: One Shot, AI Focus and AI Servo. The 400D / XTi now features the same nine-point AF system as the EOS 30D, arranged in the same diamond pattern.

In your case, you can do something like

Here is a hack to simulate the ROW_NUMBER() functionality in MySQL [1]:

4,0786 gold badges30 silver badges46 bronze badges

I think you could do this with the pseudo-code:

it'd be easier to specify it seeing the real SQL.

Mysql Php Update Multiple Columns In Mysql Download

Note the first column is specified as a constant in quotes. For this to work postcode must be a UNIQUE or PRIMARY index.

James CJames C

Anytime I see a table with columns that have 1-up counters after their names, I get concerned.

Mysql Update From Select

In general, it is a Bad Idea (TM) to store data that can be calculated from data that is already stored. What happens if your application all of a sudden needs the 4 closest postal codes? What if the postal code boundaries change?

Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.

Chris MorganChris Morgan

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.

Comments are closed.