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
– McGarnagle, Commented Mar 25, 2012 at 3:56
Related question (but not a duplicate): How to improve the speed of this SQL update query?. — Jonathan Leffler
– Jonathan Leffler, Commented Mar 25, 2012 at 4:10

egrunin · Accepted Answer · 2012-03-26 16:18:58Z

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, 2012 at 16:18

answered Mar 25, 2012 at 8:02

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

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

3 Comments

Meowbits Over a year ago

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?

Alejandro B. Over a year ago

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.

Diego Over a year ago

This is the correct answer. SQL Server Compact is VERY different from SQL Server, and does not support left outer join or table aliases.

Leigh · Accepted Answer · 2012-03-26 00:00:54Z

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, 2012 at 0:00

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

answered Mar 25, 2012 at 4:05

Sash

1,14411 silver badges24 bronze badges

3 Comments

Meowbits Over a year ago

I get an error with this one too, same as above, error at "FROM" :/

Leigh Over a year ago

There was a small typo (missing close bracket). It is now fixed.

Diego Over a year ago

SQL Server Compact is VERY different from SQL Server, and does not support left outer join or table aliases.

Collectives™ on Stack Overflow

Syntax error in SQL query

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related