Update question

[wbodger]

So, I have this code

UPDATE pcmover.Licenses
SET NumApproved='1'
FROM pcmover.Licenses L LEFT OUTER JOIN pcmover.Uses U
  ON L.ID = U.LicenseID
WHERE L.NumApproved='2'
AND U.LicenseID IS NULL

which gives me error 1064 on line 3.

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 pcmover.Licenses L LEFT JOIN pcmover.Uses U ON L.ID = U.LicenseID
WHERE L.' at line 3

And I don't see the issue. Anybody out there with any ideas?

Thanks,
Willie

 

[wbodger]

If it is any help, this query works fine

select count(KeyCode)
FROM pcmover.Licenses L LEFT JOIN pcmover.Uses U ON L.ID = U.LicenseId
WHERE L.NumApproved='2'
AND U.LicenseId IS NULL

and this one doesn't

UPDATE pcmover.Licenses
SET NumApproved='1'
FROM pcmover.Licenses L LEFT JOIN pcmover.Uses U ON L.ID = U.LicenseId
WHERE L.NumApproved='2'
AND U.LicenseId IS NULL

with the error

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 pcmover.Licenses L LEFT JOIN pcmover.Uses U ON L.ID = U.LicenseId
WHERE L.' at line 3

 

[markros]

UPDATE L
SET NumApproved='1'
FROM pcmover.Licenses L LEFT OUTER JOIN pcmover.Uses U
ON L.ID = U.LicenseID
WHERE L.NumApproved='2'
AND U.LicenseID IS NULL

 

[wbodger]

I still get the same error in the same place. Still confused...

 

[markros]

Ok, just a SWAG - what if you remove OUTER keyword from your update statement?

The update query itself looks perfectly normal to me from ANSI SQL point of view.

 

[wbodger]

Yup, tried it with and without outer. The odd thing is that if I just do a select rather than an update, it works fine.

 

[markros]

Ok, I have no experience with MySQL, I found this link

http://www.modwest.com/help/kb.phtml?qid=265&cat=6

so perhaps somehow there is a reserved keyword somewhere.

 

[r937]

the error message is telling you exactly where the problem is

you can't say UPDATE FROM -- the FROM is not valid syntax

check da manual for the valid syntax

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tsuji]

Should do it like this, moving some lines around.
[tt]
UPDATE pcmover.Licenses L
LEFT OUTER JOIN pcmover.Uses U
ON L.ID = U.LicenseID
SET NumApproved='1'
WHERE L.NumApproved='2'
AND U.LicenseID IS NULL
;[/tt]

 

[wbodger]

Yup, that was it, thank you. Then, I moved on to the next set of updates,a little more involved (not much, but there is an aggregate column in the selection criteria). So, the idea behind the code is this

UPDATE pcmover.Licenses L
JOIN pcmover.Uses U
ON L.ID = U.LicenseID
SET NumApproved='1'
WHERE L.NumApproved='2'
HAVING count(U.LicenseID) = '1'

I want to update every license that has 2 uses approved but has only been used one time. I can quickly select those, but when I try the update, I get

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 'HAVING count(U.LicenseID) = '1'' at line 6

But if I remove the Having, then I don't know how many times the SN has been used and I can't decrease the numapproved to 1 if it has been used twice.

Any more thoughts?

Willie

 

[tsuji]

Maybe this? or something along this line.
[tt]
SET @v:=(SELECT count(U.LicenseID) FROM pcmover.Uses U INNER JOIN pcmover.Licenses L ON L.ID=U.LicenseId AND L.NumApproved='2' GROUP BY U.LicenseID HAVING count(U.LicenseID)=1 LIMIT 1);

UPDATE pcmover.Licenses L
JOIN pcmover.Uses U
ON L.ID = U.LicenseID
SET NumApproved='1'
WHERE L.NumApproved='2' AND @v IS NOT NULL
;
[/tt]

 

[tsuji]

Thinking more about it, maybe that's not restrictive enough.

 

[tsuji]

Okay, this is what I can contribute on the idea and after I won't bother you. It consists on looping this block until @v is null.
[tt]
-- loop this block until @v is null
SET @v:=(SELECT [blue]U.LicenseID[/blue] FROM pcmover.Uses U INNER JOIN pcmover.Licenses L ON L.ID=U.LicenseId AND L.NumApproved='2' GROUP BY U.LicenseID HAVING count(U.LicenseID)=1 LIMIT 1);

UPDATE pcmover.Licenses L
JOIN pcmover.Uses U
ON L.ID = U.LicenseID
SET NumApproved='1'
WHERE L.NumApproved='2' AND [blue]U.LicenseID=@v[/blue]
;
[/tt]

 

[tsuji]

One of the difficulty is to get the comma delimited list of U.LicensID or L.ID. I now have the resolution of it through group_concat() and can propose this.
[tt]
SET @v:=(SELECT group_concat(X.Y) FROM (SELECT U.LicenseID Y FROM pcmover.Uses U INNER JOIN pcmover.Licenses L ON L.ID=U.LicenseId AND L.NumApproved=2 GROUP BY U.LicenseID HAVING count(U.LicenseID)=1) X);

SET @s:=concat("UPDATE pcmover.Licenses L JOIN pcmover.Uses U ON L.ID = U.LicenseID SET NumApproved=1 WHERE L.NumApproved=2 and U.LicenseID IN (",@v,")");

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stml;
[/tt]

 

[wbodger]

Please forgive my ignorance, but where/how would I run something like this?