0

I want to execute the following SQL statement in PhP. I have simplified it below. I would like to know the MySQL modifications to make it work. I tried a few like giving aliases to the tables but it did not work:

     update T1 set col1 = 500 
         where  col2 = 12345 and 
                col3 = (select max(col3) from T1 where  col2 = 12345)

I want to update a record which has the max value for one column. There is only one table involved in the entire query. I am using PDO, if that is relevant.

The error given is:

#1093 - You can't specify target table 'T1' for update in FROM clause
Sunny
  • 9,245
  • 10
  • 49
  • 79
  • possible duplicate of [Using a SELECT statement within a WHERE clause](http://stackoverflow.com/questions/6424301/using-a-select-statement-within-a-where-clause) – wmfrancia Dec 26 '13 at 17:49
  • This one involves an update. Different beast altogether. See @Gordon's answer below. – Sunny Dec 26 '13 at 18:20

1 Answers1

1

Use a join instead:

 update T1 join
        (select max(col3) as maxcol3
         from T1 t11
         where col2 = 12345
        ) tmax
        on T1.col3 = tmax.maxcol3 and
           T1.col2 = 12345
     set T1.col1 = 500;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • WOW! And I thought I knew SQL! It works. I had a bit of a problem with the real query but I worked around it. It also took some time to understand the syntax as I am more used to the "where" usage in which the set comes right after the update tablename. Thanks a lot. – Sunny Dec 26 '13 at 18:19
  • 1
    @Samir . . . Your original syntax is standard SQL and works in most databases. This form of the `update` with `join` is specific to MySQL (SQL Server and Postgres support `update`/`join` but using a `from` clause). – Gordon Linoff Dec 26 '13 at 18:23
  • If you can response to this, would appreciate. I do not get the importance of the t11 alias? Reason I ask is that I would have expected the same update to go through multiple times but it does not the second time around. Returns zero rows updated second time but correctly updates the first time. – Sunny Dec 28 '13 at 13:31
  • 1
    The `t11` alias is not needed. I don't think MySQL records an "update" when the new value is the same as the old value. – Gordon Linoff Dec 28 '13 at 13:55
  • Thanks! Gordon. I was hoping you would respond. The value of col1 is already set to 500. True. But the MySQL rowcount() function should still return 1 or greater than zero with no other db activity. It is returning zero. No syntax error. When I change the value of 500 (which actually is UNIX time), I do get sometimes unpredictably (or may be I did not notice the pattern) a row count > 0 – Sunny Dec 28 '13 at 15:40
  • I copy pasted my comment from the other question I recently posted without reading your comment here. Your comment here, answers my question if indeed the rowcount is 0 when the old value is equal to new value. Traditionally that is, however, not been true with standard SQL with Oracle and DB2, at least the older versions. – Sunny Dec 28 '13 at 15:42
  • 1
    @Samir . . . And, in fact, it has changed in MySQL in different versions. I don't recall which way, but you can google and find out. – Gordon Linoff Dec 28 '13 at 15:43