[thelist] SQL rank tip correction
Joshua OIson
joshua at alphashop.net
Thu May 10 17:07:24 CDT 2001
<grumble grumble>
I messed up with that last tip.
The WHERE clause in the 2nd option should read
WHERE mytable.id = inserted.id
Sorry bout that
-joshua
> 2. You can set up a trigger to update the rank automatically:
>
> CREATE TRIGGER update_rank ON mytable
> FOR INSERT AS
> UPDATE mytable
> SET mytable.rank = (SELECT COALESCE(MAX(rank),0) FROM mytable)+1
> FROM inserted
> WHERE mytable.id = mytable.id
becomes
> CREATE TRIGGER update_rank ON mytable
> FOR INSERT AS
> UPDATE mytable
> SET mytable.rank = (SELECT COALESCE(MAX(rank),0) FROM mytable)+1
> FROM inserted
> WHERE mytable.id = inserted.id
More information about the thelist
mailing list