Mysql updating multiple rows

17-Apr-2016 11:28

:) Anwsers can be in simple SQL or with the use of php (and Code Igniter) or in a different way.

I'd this problem to be solved in any possible way ;) I can really use the advice/help!

=D Update My Table SET value = CASE WHEN game_id = 1 AND x = -4 AND y = 8 THEN 1 WHEN game_id = 1 AND x = -3 AND y = 7 THEN 2 WHEN game_id = 2 AND x = 5 AND y = 2 THEN 3 ELSE value END WHERE game_ID IN (1,2,3) AND -- the purpose of this WHERE clause x IN (-4, -3, 5) AND -- is to optimize the query by preventing from y IN (8,7,2) -- performing full table scan.

PDF (US Ltr) - 37.7Mb PDF (A4) - 37.7Mb PDF (RPM) - 37.0Mb HTML Download (TGZ) - 10.2Mb HTML Download (Zip) - 10.3Mb HTML Download (RPM) - 8.9Mb Man Pages (TGZ) - 214.7Kb Man Pages (Zip) - 327.7Kb Info (Gzip) - 3.4Mb Info (Zip) - 3.4Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 option as part of a table reference.

(This is because the order in which the rows are updated determines which rows are ignored.) With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using mode.

INSERT INTO table_users (cod_user, date, user_rol, cod_office) VALUES ('622057', '12082014', 'student', '123456'), ('2913659', '12082014', 'assistant','123456'), ('6160230', '12082014', 'admin', '123456') ON DUPLICATE KEY UPDATE cod_user=VALUES(cod_user), date=VALUES(date) combination is a primary key.Looping though, it builds the query and executes it.The array can then come from a variety of sources (results from another query, form inputs or, as in this case, hard-coded values) Update: If you really need to execute all in one, heres the PHP info on multi query: mysqli::multi_query This example shows updating two different columns in two different rows so you can expand this to more rows and columns by cludging together a query like this.That does when I try to update a record with the same value it already had, is there any way to skip the update if the current value is the same as being updated?

My SQL allows a more readable way to combine multiple updates into a single query.

Please see this answer for another, more elaborate research.