Oracle update statement not updating Dirty male chatrooms

07-Nov-2019 19:19

It's amazing what SQL can do once you start digging into it.

A little gotcha about CASE statements is that you can only return simple values in your THEN clauses. I have many stored procedures update the same table but different fields, and I am thinking to merge them into one sproc.

create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.

I am working on a transactional system that allows for the voiding of account credits.As part of the Void process, I need to make sure that I never let an account balance drop below zero dollars (business requirement - these aren't "real" dollars).In something like Cold Fusion, I would use the Max() function: I am hard coding the variables here, but you get the point. I guess there's nothing about it that should have made me think that it wouldn't work; I just never tried this technique before.I don't know if something like this works on My SQL but on SQL Server you can do something like the following UPDATE p SET p.col1 = p.col1 q.other Col FROM table1 AS p join table2 AS q ON p.fk_table2_pk = WHERE q.filter Column = 'something' @Ben/John, I had just learned you could do joins in an UPDATE SQL statement early last month.I should have guessed you could since the FROM statement is there and joining would making sense if you need to bring data from other locations.

I am working on a transactional system that allows for the voiding of account credits.

As part of the Void process, I need to make sure that I never let an account balance drop below zero dollars (business requirement - these aren't "real" dollars).

In something like Cold Fusion, I would use the Max() function: I am hard coding the variables here, but you get the point. I guess there's nothing about it that should have made me think that it wouldn't work; I just never tried this technique before.

I don't know if something like this works on My SQL but on SQL Server you can do something like the following UPDATE p SET p.col1 = p.col1 q.other Col FROM table1 AS p join table2 AS q ON p.fk_table2_pk = WHERE q.filter Column = 'something' @Ben/John, I had just learned you could do joins in an UPDATE SQL statement early last month.

I should have guessed you could since the FROM statement is there and joining would making sense if you need to bring data from other locations.

The following script executes properly when an insert statement is issued, however, when the same script is used to execute an update statement, the process freezes.