fix query 1

[peac3]

HI guys,

I was trying to update the table from command below but it's not working...

Can you please shed some light to make it work.

      UPDATE      table
      SET   amount =
            CASE  
                        WHEN flag = 'Y' AND limit_amt > credit_amt THEN limit_amt/er.rates
                        ELSE credit_amt/er.rates END
      from table t LEFT OUTER JOIN exchange_rate er on t.cncy = er.cncy

Thanks in advance

 

[bborissov]

Where is your WHERE clause?

What if there is No records in exchange_rate that match the cncy?


Borislav Borissov
VFP9 SP2, SQL Server

 

[peac3]

There is no WHERE clause, it applies to entire data in the table.


if there is No records in exchange_rate that match the cncy -> theoritically it wouldnt be the case as it will be trapped in the previous stage but lets say put it NULL.

Thanks bborissov.

 

[bborissov]

When you ALIAS the table you must use that ALIAS everywhere in the query:

UPDATE [COLOR=red][b]t[/b][/color] SET amount = CASE WHEN flag = 'Y' AND limit_amt > credit_amt 
                                THEN limit_amt/er.rates
                      ELSE credit_amt/er.rates END
from table t
LEFT OUTER JOIN exchange_rate er on t.cncy = er.cncy

Borislav Borissov
VFP9 SP2, SQL Server