mySQL: Sub SELECT statement within UPDATE command

Question

I'm trying to update a column in visitors. I'm also using a sub select statement for the SET part of the update query.

UPDATE
    visitors AS v
SET
    v.IsFirstVisit = (SELECT COUNT(*) FROM visitors AS v2 WHERE ..... LIMIT 1)

However, mySQL returns this error message

#1093 - You can't specify target table 'v' for update in FROM clause

I have no clue why I can't access the 'v' object within the inner select statement. I also don't want to use multiple statements as this would cause a performance issue.

Question: How can I use the 'v' object within the inner select?

Update: This is the entire query

UPDATE
    visitors AS v
SET
    IsFirstVisit = (SELECT Count(*) FROM visitors AS v2 WHERE v2.VisitorId < v.VisitorId AND v2.IP = v.IP AND v2.DateTime > v.DateTime [TODO:SUBTRACT30MINUTES] LIMIT 1)
WHERE
    VisitorId = "991"

echo_Me · Accepted Answer · 2013-02-20 18:10:19Z

1

i guess you looking for this

 UPDATE
    visitors 
 SET
    IsFirstVisit = (SELECT COUNT(*) FROM visitors  WHERE ..... LIMIT 1)

edit:

try this

    UPDATE
       visitors
    SET
         IsFirstVisit = (SELECT Count(*) FROM visitors v2 inner join visitors v 
         ON v.VisitorId = v2.VisitorId WHERE v2.IP = v.IP AND v2.DateTime > v.DateTime AND v2.VisitorId < v.VisitorId [TODO:SUBTRACT30MINUTES] LIMIT 1)
    WHERE
    VisitorId = "991"

edited Feb 20, 2013 at 18:10

answered Feb 20, 2013 at 17:03

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bytecode77 Over a year ago

That would not work since I have to access both visitor objects within the "...." of the WHERE clause.

bytecode77 Over a year ago

I added the whole query to the question.

bytecode77 Over a year ago

In your new solution, the inner select statement does not interact with the outer one, but I think I can just insert the VisitorId from within PHP there. That'll work. Thank you.

Muhammad Hani · Accepted Answer · 2013-02-20 17:10:35Z

1

The inner join in UPDATE statement won't be a bad idea.

UPDATE
    visitors inner join (SELECT COUNT(*) as test FROM visitors v) as v
 SET
    isfistvisit = v.test;

Another workaround which Im not a big fan of it.

update visitors
   set isfistvisit = (
      select count(*) from (
         select count(*) from visitors
      ) as x
      )

Demo

answered Feb 20, 2013 at 17:10

Muhammad Hani

8,6725 gold badges32 silver badges44 bronze badges

Collectives™ on Stack Overflow

mySQL: Sub SELECT statement within UPDATE command

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related