How to use joins within Update statement in HyperSQL DB?

[Ali Uzair]

Hi,
I am new to HSQL DB and am trying to translate few sybase query to HSQL to get a grasp of HSQL. I have a the following query, how will I translate it in HSQL DB.

UPDATE Table1
SET NewBalance = convert(NUMERIC(16, 2), A.Balance* B.charge)
FROM tempdb..Table1 A, Table2 B
WHERE A.UserID = @Userid
AND A.Store = B.Store
AND B.Date = @Date
AND B.Category = @Category

[Ali Uzair]

translated the query as pasted below.

UPDATE Table1 A
SET A.NewBalance = (SELECT convert( A.Balance B.charge, DECIMAL(16, 2))
FROM Table2 B
WHERE A.Store = B.Store
AND B.Date = Date
AND B.Category = Category )
WHERE A.UserID = userid;*

[Fred Toussi]

You cannot have a FROM clause in an update statement itself. A subselect is used as you have translated.

[Ali Uzair]

thanks fred for confirming