CODEWITHSUNDEEP
mysql
Seehyung Lee
Seehyung Lee

Reputation: 610

Combine Update and Select Query

I got two MySQL working fine and i'm trying to find a way to combine them into one single query.

First, it selects ID of an employee.

SELECT 'ID' FROM `employee` ORDER BY ID DESC LIMIT 1;

Let's say it returns ID 100;

Then update data of employees whose ID is 100 

UPDATE 'LOG' SET `TIME_EXIT`='2013/02/22' WHERE `ID`='100';

Can i do it all in a single query?

Upvotes: 1

Views: 17279

Answers (2)

Florianer
Florianer

Reputation: 1

It's now a few months old, but maybe helps you or others finding this via google…

If you want to UPDATE a field in the same selected table use this:

UPDATE LOG SET
    TIME_EXIT = '2013/02/22' 
WHERE ID = (
        SELECT ID
        FROM (
            SELECT ID
            FROM LOG
            WHERE whatEverYouWantToCheck = whateverYouNeed
        ) AS innerResult 
    )

So, you SELECT id from a subselect. If you try to subselect it directly, mySQL quites with your error message You can't specify target table 'log' for update in FROM clause, but this way you hide your subsubquery in a subquery and that seems to be fine. Don't forget the AS innerResult to avoid getting the error message #1248 - Every derived table must have its own alias. Also match the subsubquery field name to the subquery field name in case you do something like SELECT COUNT(*) or SELECT CONCAT('#', ID)

Upvotes: 0

sjdaws
sjdaws

Reputation: 3536

Just add them together:

UPDATE LOG SET TIME_EXIT = '2013/02/22' 
WHERE ID = (
              SELECT ID
              FROM employee
              ORDER BY ID DESC
              LIMIT 
            );

But based on that code currently it'll only ever update the last employee, you will need to select the correct employee by using some other identifier to ensure you have the correct one.

UPDATE LOG SET TIME_EXIT = '2013/02/22' 
WHERE ID = (
              SELECT ID 
              FROM employee 
              WHERE NAME = 'JOHN SMITH' 
              ORDER BY ID DESC 
              LIMIT 1
            );

Upvotes: 5

Related Questions