CODEWITHSUNDEEP
sqldatabasevb.net
Meowbits
Meowbits

Reputation: 586

Syntax error in SQL query

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

Upvotes: 0

Views: 2255

Answers (2)

egrunin
egrunin

Reputation: 25053

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)

Upvotes: 1

Sash
Sash

Reputation: 1144

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

Upvotes: 0

Related Questions