This is really strange. When I run this query in psql, I get "UPDATE
1" and it works. When I run it with ns_db, I get no errors, but my
row doesn't get updated, either. Here is the query:
update users set money = money - coalesce((select cost from weapons w
where id = $w_id and w.user_id is null), 0) where user_id =
$user_id
(I've logged the actual sql to make sure the tcl variables are
working correctly, and they are.)
In desperation I tried wrapping the coalesce in another subselect.
It also works in psql, but not in ns_db:
update users set money = money - (select coalesce((select cost from
weapons w where id = $w_id and w.user_id is null), 0) from dual)
where user_id = $user_id
Any idea what could be happening? What is stranger is that it this
is part of a transaction; the first statement in the transaction gets
executed fine. Here is that code:
set update_store "update weapons set user_id = $user_id where id =
$w_id"
set update_money "update users set money = money - coalesce((select
cost from weapons w where id = $w_id and w.user_id is null), 0) where
user_id = $user_id"
set db [ns_db gethandle]
with_transaction $db {
ns_db dml $db $update_store
ns_db dml $db $update_money
} { ... }
(removing the transaction has no effect.)
What am I missing here?
