Syntax error in SQL query

Question

I am having problems implementing this query in vb.net. The error message that I am getting is with the "as" in the first line. This is a local sql compact database 3.5

        cmd.CommandText = "UPDATE player as a " &
                            "SET starter = 'TRUE' " &
                            "WHERE NOT EXISTS (SELECT '1' " &
                            "FROM player AS b " &
                            "WHERE(b.school = a.school) " &
                            "AND b.weight = a.weight " &
                            "AND b.skill > a.skill)"
        cmd.ExecuteNonQuery()

Error message - http://i40.tinypic.com/34gms5z.png

        cmd.CommandText = "UPDATE a " &
                "SET starter = 'TRUE' " &
                "FROM player a " &
                "LEFT JOIN player b " &
                "ON a.school = b.school " &
                "AND a.weight = b.weight " &
                "AND b.skill > a.skill " &
                "WHERE b.school is NULL"
        cmd.ExecuteNonQuery()

Error message - http://i40.tinypic.com/106kn86.png

I'm having trouble parsing your SQL ... what are you trying to do with that query? UPDATE: I see now, you're trying to make everyone a starter, than whom there's no more skilled player of the same weight at the same school — McGarnagle, Mar 25 '12 at 03:56
Related question (but not a duplicate): [How to improve the speed of this SQL update query?](http://stackoverflow.com/questions/9847599/how-to-improve-the-speed-of-this-sql-update-query). — Jonathan Leffler, Mar 25 '12 at 04:10

egrunin · Accepted Answer · 2012-03-26T16:18:58.090

1

Does this work?

UPDATE player
SET starter = 'TRUE' 
WHERE NOT EXISTS 
(
    SELECT * FROM player b 
    WHERE b.school = player.school
    AND b.weight = player.weight 
    AND b.skill > player.skill
)

Edited to add:

This will probably run faster if you create an index:

CREATE INDEX player_school_weight ON player (school, weight, skill)

edited Mar 26 '12 at 16:18

answered Mar 25 '12 at 08:02

egrunin

24,650
8
50
93

YES!, this appears to be working. However it is pretty slow. If I made it a stored procedure would it be much faster you think? – Meowbits Mar 25 '12 at 08:47
No, writting it as a Stored procedure would NOT make it MUCH faster. The only speeding would be given because the query would be compiled beforehand and won't be needed to recompile it on each run. Diego's way may be faster, if you get it to run :) Otherwise, you'd have to check your indexes. – Alejandro B. Mar 25 '12 at 17:00
This is the correct answer. SQL Server Compact is VERY different from SQL Server, and does not support `left outer join` or table aliases. – Diego Mar 26 '12 at 14:13

score 0 · Answer 2 · edited Mar 26 '12 at 00:00

0

I believe that you want this:

UPDATE pl 
SET 
    starter = 'True'
FROM 
    [Player] pl
LEFT JOIN 
    [Player] pl2 ON (pl.[School] = pl2.[School])
    AND 
    (pl.[Weight] = pl2.[Weight])
    AND 
    (pl2.[Skill] > pl.[Skill])
WHERE pl2.[School] IS NULL

edited Mar 26 '12 at 00:00

Leigh

28,765
10
55
103

answered Mar 25 '12 at 04:05

Sash

1,134
11
23

I get an error with this one too, same as above, error at "FROM" :/ – Meowbits Mar 25 '12 at 04:38
There was a small typo (missing close bracket). It is now fixed. – Leigh Mar 26 '12 at 00:02
SQL Server Compact is VERY different from SQL Server, and does not support `left outer join` or table aliases. – Diego Mar 26 '12 at 14:15

Syntax error in SQL query

2 Answers2