Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql

by

This is a typical MySQL thing and can usually be circumvented by selecting from the table derived, i.e. instead of

FROM manager AS m2

use

FROM (select * from manager) AS m2

The complete statement:

UPDATE manager
SET status="Y"
WHERE branch_id IN
(
  select branch_id
  FROM (select * from manager) AS m2
  WHERE (branch_id, year) IN
  (
    SELECT branch_id, year
    FROM branch_master
    WHERE type="finance"
  )
);

More Related Contents:

  1. MySQL: is a SELECT statement case sensitive?
  2. Check overlap of date ranges in MySQL
  3. 1114 (HY000): The table is full
  4. UPDATE multiple tables in MySQL using LEFT JOIN
  5. How to raise an error within a MySQL function
  6. What is maximum query size for mysql?
  7. Finding all parents in mysql table with single query (Recursive Query)
  8. How do I retrieve my MySQL username and password?
  9. How to execute a MySQL command from a shell script?
  10. Select most common value from a field in MySQL
  11. MySQL update a joined table
  12. Returning the ‘last’ row of each ‘group by’ in MySQL
  13. Optimal MySQL settings for queries that deliver large amounts of data?
  14. mysql configuration stops at “starting server”
  15. How to change the default port of mysql from 3306 to 3360
  16. Is it possible to reference one column as multiple foreign keys?
  17. No Entity Framework provider found for ‘MySql.Data.MySqlClient’ ADO.NET provider
  18. How to Import Multiple csv files into a MySQL Database
  19. MySQL error #2014 – Commands out of sync; you can’t run this command now
  20. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  21. Row to column transformation in MySQL
  22. Split a string and loop through values in MySQL stored procedure
  23. MySQL Select Query – Get only first 10 characters of a value
  24. Create an index on a huge MySQL production table without table locking
  25. MySQL concurrency, how does it work and do I need to handle it in my application
  26. mysql select id and name from other table and join query
  27. The web application [] appears to have started a thread named [Abandoned connection cleanup thread] com.mysql.jdbc.AbandonedConnectionCleanupThread
  28. Execute INSERT if table is empty?
  29. MySQL data directory location
  30. INSERT INTO fails with node-mysql

Leave a Comment