CODEWITHSUNDEEP
mysqlsql
lufizi
lufizi

Reputation: 397

How to select the last row from one table then update the last row of another one?

I'm doing this

    UPDATE table1
    SET table1.col1 = table2.col2
    FROM table2 WHERE table1.id = (SELECT MAX(id) FROM table1) AND table2.ID = (SELECT MAX(ID) FROM table2);

I have a syntax error, but I don't know how to do it.

Upvotes: 0

Views: 1623

Answers (2)

richard ordoñez
richard ordoñez

Reputation: 71

MySQL

   update table1
    cross join (
        select col2
        from table2
        order by id desc
        limit 1) as A
    inner join (
        select id
        from table1
        order by id desc
        limit 1) as B on (B.id = table1.id)
    set table1.col1 = A.col2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Assuming that id is unique in table1:

UPDATE table1 t1
    SET t1.col1 = (select t2.col2 from table2 t2 order by id desc limit 1) 
    ORDER BY t1.id DESC
    LIMIT 1;

This updates the "last" row in table1 (by id) with the "last" row in table2 (by id).

Your syntax doesn't work in multiple ways:

  • MySQL does not support the FROM clause in UPDATE.
  • MySQL does not allow you to reference the table being updated in a subquery.

Upvotes: 1

Related Questions