MySQL – UPDATE query based on SELECT Query

You can actually do this one of two ways:

MySQL update join syntax:

UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

ANSI SQL syntax:

UPDATE tableA SET validation_check = 
    (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
        FROM tableA
        INNER JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Pick whichever one seems most natural to you.

Related Posts:

  1. mysql update multiple columns with same now()
  2. MySQL error code: 1175 during UPDATE in MySQL Workbench
  3. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  4. MySQL Select Multiple VALUES
  5. MySQL – UPDATE query with LIMIT
  6. MySQL Rank & $wpdb
  7. How to search usermeta table
  8. SELECT statement wrong?
  9. Column count doesn’t match value count at row 1
  10. MySQL error 2006: mysql server has gone away
  11. ERROR 2003 (HY000): Can’t connect to MySQL server on localhost (10061)
  12. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  13. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  14. What is the definition of cardinality in SQL
  15. MySQL SELECT increment counter
  16. what does “/index.php?admin/admin_dashboard” in my url mean
  17. How to join two tables by multiple columns in SQL?
  18. How to get size of mysql database?
  19. SQL – Update multiple records in one query
  20. Difference between VARCHAR and TEXT in MySQL
  21. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  22. mysqladmin: connect to server at ‘localhost’ failed
  23. Usage of MySQL’s “IF EXISTS”
  24. How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’
  25. Simple Random Samples from a Sql database
  26. What does SQL clause “GROUP BY 1” mean?
  27. LAST_INSERT_ID() MySQL
  28. SQL select only rows with max value on a column
  29. Trying to perform MINUS operation in MySQL
  30. MySQL IF NOT NULL, then display 1, else display 0
  31. How do I find which transaction is causing a “Waiting for table metadata lock” state?
  32. MySQL – UPDATE multiple rows with different values in one query
  33. mysql count duplicates
  34. phpmyadmin #1045 Cannot log in to the MySQL server. after installing mysql command line client
  35. MySQL config file location – redhat linux server
  36. Create boolean column in MySQL with false as default value?
  37. Is there a naming convention for MySQL?
  38. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
  39. Error Code 1292 – Truncated incorrect DOUBLE value – Mysql
  40. MySql – HAVING vs WHERE 
  41. How to stop mysqld
  42. Is merge statement available in MySQL
  43. GROUP_CONCAT comma separator – MySQL
  44. Error: Duplicate entry ‘0’ for key ‘PRIMARY’
  45. Why are simple updates to wp_postmeta’s “_edit_lock” so slow?
  46. Set page template for all pages?
  47. Optimize Multiple Taxonomy Term MySQL Query?
  48. WordPress MySQL & PHP Performance
  49. What specific database privileges does WordPress need?
  50. Using source control with WordPress
  51. My WordPress site always displays a cached version of its homepage
  52. Updating User Meta with SQL Query
  53. query to create woocommerce products from images
  54. wpdb prepare syntax
  55. How to run this SQL query of wp_terms database
  56. Proper way to work with wpdb to get user’s firstname
  57. How to change post format from Aside to Image for all posts?
  58. Inserting Hundreds of Thousands of Posts at Once
  59. wp_admin edit.php slow with lots of queries
  60. How to add and SQL query of posts only published
  61. Slow Mysql Queries
  62. can database and media folder be moved to a different drive?
  63. Find duplicate posts with same slug and author
  64. writing inner join in wpdb
  65. How to get data from WordPress site sql file
  66. Unknown collation when I import a dump from an existing site into a development database?
  67. PHP script can’t connect to Database on port 3307
  68. WordPress with php5-fpm and nginx
  69. When WordPress Default table, Echo Success. When Plugin Table, Echo Blank
  70. How to translate this mySQL query to $wpdb query
  71. $wpdb to insert html form information
  72. Proper usage of $wpdb->update()?
  73. MySQL 5.5 upgrade ‘broke’ WordPress [closed]
  74. Separate DB by yearly archive of WordPress
  75. Paypal Post IPN handeling nightmare
  76. What should a WordPress developer know about MySQL? [closed]
  77. Copyright: Get first and last date of post type
  78. Update MySQL query so that it functions again
  79. Query posts by custom taxonomy and sort by post_modified
  80. WordPress $wpdb get posts from category and sort by custom meta
  81. Array Formation Issue
  82. Migration problems with mysql: Operation not allowed when innodb_forced_recovery > 0 [closed]
  83. WordPress $wpdb no result
  84. (WP List Table) How to sort by SKU and Custom Fields?
  85. User set date and time from frontend doesn’t get stored into database
  86. when using $wpdb sudden connection rejection without changing anything. Access denied, with errors from repair tool
  87. How to bulk delete a certain part of all wordpress posts
  88. edit_user_created_user hook – using to update Groups
  89. Mysql Error in querying form entries in Gravity Form plugin
  90. update_option returns false in AJAX, how to debug?
  91. Improving performance of SQL sub-queries in WordPress
  92. Saving custom term value to the database in new table
  93. MySQL errors after a WordPress clone
  94. MYSQL Join on meta key value?
  95. Complicated MySQL Query
  96. problem with quotes on new post
  97. Where other than wp-config can database access details be defined?
  98. Why does the MySQL command line tool ignore the –port parameter?
  99. Postgres equivalent to MySQL’s \G?
  100. Possible SQL injection. How to locate and fix?

Leave a Comment