CODEWITHSUNDEEP
mysqlsql
Lee Colarelli
Lee Colarelli

Reputation: 151

How can i update the results of a MySQL select query

I have the following SELECT query...

SELECT users.id, users.firstname, users.lastname, users.email,
users.live, users.editoverride, 
usertenderstage.tenderId, 
usertenderstage.stageId, usertenderstage.statusId
FROM users
JOIN usertenderstage ON usertenderstage.userId = users.id
WHERE users.live = 1 AND usertenderstage.tenderId = 1 
AND usertenderstage.stageId = 2 AND usertenderstage.statusid = 6

I am trying to edit the query to make it an UPDATE query, and set users.editoverride = 1. My attempt is below...

UPDATE users.id, users.firstname, users.lastname, users.email,
users.live, users.editoverride, 
usertenderstage.tenderId, 
usertenderstage.stageId, usertenderstage.statusId
FROM users
JOIN usertenderstage ON usertenderstage.userId = users.id
SET users.editOverride = 1
WHERE users.live = 1 AND usertenderstage.tenderId = 1 
AND usertenderstage.stageId = 2 AND usertenderstage.statusid = 6

But I am getting error message 

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'FROM users
JOIN usertenderstage ON usertenderstage.userId = users.id
SET users' at line 5

Could someone please help me to know what I'm doing wrong? Thanks.

Upvotes: 0

Views: 501

Answers (1)

Pranav Prakash
Pranav Prakash

Reputation: 869

Following should work:

UPDATE users
SET editoverride = 1
WHERE id IN
    (SELECT users.id
     FROM users
     JOIN contractorscontractsappliedfor ON contractorscontractsappliedfor.contractorId = users.id
     JOIN usertenderstage ON usertenderstage.userId = users.id
     WHERE users.live = 1
       AND contractorscontractsappliedfor.contractid = 1
       AND usertenderstage.stageId = 2
       AND usertenderstage.statusid = 6)

It seems the issue is with MySQL as you are getting error "#1093 - You can't specify target table 'users' for update in FROM clause". To get around, nest it one level deep as below:

UPDATE users
SET editoverride = 1
WHERE id IN
    (SELECT A.id
     FROM
       (SELECT users.id
        FROM users
        JOIN contractorscontractsappliedfor ON contractorscontractsappliedfor.contractorId = users.id
        JOIN usertenderstage ON usertenderstage.userId = users.id
        WHERE users.live = 1
          AND contractorscontractsappliedfor.contractid = 1
          AND usertenderstage.stageId = 2
          AND usertenderstage.statusid = 6) AS A)

I haven't tested it as don't have MySQL handy.

Upvotes: 1

Related Questions