Decode function in Update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Decode function in Update statement

Author

Topic

aoetitan
Starting Member

1 Post

Posted - 2011-08-29 : 22:07:17

Hello everyone,

I'm trying to write a query where I can update a pastdue_fees column based on a difference between return_dte and due_dte columns.

I am using Oracle SQL. This is what I have so far for my decode function:

SQL> SELECT
2 DECODE(SIGN((return_dte - due_dte)*2),
3 '-1', '0',
4 '1', '12', 'Null')
5 FROM book_trans;

DECO
----
Null
12
Null
0

So the logic is that if the sign is -1, the value in return_dte column should be 0; if it's +1 then it's 12 and everything else is Null.

So now, I need to enter my decode function into the update statement to update the columns. However, I get error messages:

The logic should be:
UPDATE book_trans SET PastDue_fees = decode(expression)

I've given it a couple of different tries with the following results:

SQL> UPDATE book_trans
2 SET pastdue_fees = SELECT
3 DECODE(SIGN((return_dte - due_dte)*2),
4 '-1', '0',
5 '1', '12', 'Null')
6 FROM book_trans;
SET pastdue_fees = SELECT
*
ERROR at line 2:
ORA-00936: missing expression

SQL> UPDATE book_trans
2 SET pastdue_fees =
3 DECODE(SIGN((return_dte - due_dte)*2),
4 '-1', '0',
5 '1', '12', 'Null')
6 FROM book_trans;
FROM book_trans
*
ERROR at line 6:
ORA-00933: SQL command not properly ended

Any help or tips would be greatly appreciated as I've been taking SQL for about six weeks and not very proficient!

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-29 : 22:26:13

For Oracle try posting at dbforums.com SQLTeam.com is on SQL Server

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources