UPDATE STATEMENT 3

[acct098]

I need help fixing the update statement below.
The update statement updates all of the records on the table for a specific day. This is not what I want to happen.
I would like the cleared field updated with an ‘N’ if the concatenated fields is found in a subset of concatenated fields (ref subquery) where the edate +1
Does anyone know how the fix the update statement below
So that the only the appropriate records will be updated?


UPDATE SUSF SET CLEARED = 'N'
WHERE EDATE + 1 IN (SELECT EDATE FROM SUSF)
AND USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM in (SELECT USERID_2||B_C||B_AO||B_NUM||T_C||AO||T_NUM
FROM SUSF)
AND EDATE IN ('01-DEC-06' , '30-NOV-06' );


EDATE USERID_2||B_C||B_AO||B_NUM||T_C||AO||T
--------- --------------------------------------
30-NOV-06 ZTI741TO10Z15129TO10741T71587
01-DEC-06 ZTI741TO10Z15129TO10741T71587
01-DEC-06 ZTI741TO10Z20454TO10741T71672
30-NOV-06 ZTI741TO10Z20454TO10741T71672
01-DEC-06 ZTI792TP10 TP10792T7150201
30-NOV-06 ZTI792TP10 TP10792T7150201

 

[LKBrwnDBA]

You could try something like this:

UPDATE SUSF u SET CLEARED = 'N' 
 WHERE EDATE IN ('01-DEC-06' , '30-NOV-06' )
   AND EXIST (
   SELECT '?' FROM SUSF q
    WHERE q.EDATE = u.EDATE + 1 
      AND q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM 
        = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[acct098]

Im still stuck. Any ideas on how to fix the code below:


UPDATE SUSF SET CLEARED = 'N'
WHERE EDATE IN ('01-DEC-06' , '30-NOV-06')
AND Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM
EXISTS (
SELECT U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM FROM SUSF q
WHERE Q.EDATE = U.EDATE + 1
AND Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM
= U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM)
/

 

[SantaMufasa]

I'm still stuck.

Are you receiving a

a) syntax error?
b) run-time error?
c) logic error?

Whichever it is, could you please post the output artifacts of the query (i.e., error messages or bad output; if "bad output", please also post a copy of the "good output" you want.)



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acct098]

SQL> UPDATE SUSF SET CLEARED = 'N'
2 WHERE EDATE IN ('01-DEC-06' , '30-NOV-06')
3 AND Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM
4 EXISTS (
5 SELECT U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM FROM SUSF q
6 WHERE Q.EDATE = U.EDATE + 1
7 AND Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM
8 = U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM)
9 /
EXISTS (
*
ERROR at line 4:
ORA-00920: invalid relational operator

 

[carp]

When asking a question like "How can I fix this?", it would be very helpful if you would include some indication about why you think there is something wrong (error messages, wrong output, etc).
With that said, I think you are running into trouble because you are missing the table alias "U" in the first line. Also, Q is undefined outside of your subquery. Is there a reason that LKBrwnDBA's code won't work for you?

 

[carp]

You have several problems - your table aliases are twisted up and you are not using EXISTS correctly. Please compare your update statement to the one supplied by LKBrwnDBA - but change EXIST to EXISTS.

 

[SantaMufasa]

Is there a reason that LKBrwnDBA's code won't work for you?

Actually, yes...LK's code needs to use the operator, EXISTS, instead of the syntactically incorrect EXIST.

In your code, ACCT, it might work if you remove the extraneous expression prior to the EXISTS operator to read, instead as:

SQL> UPDATE SUSF SET CLEARED = 'N' 
  2   WHERE EDATE IN ('01-DEC-06' , '30-NOV-06')        
  3    AND  -- [b]remove this expression: Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM[/b]
  4      EXISTS (        
  5  SELECT U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM FROM SUSF q 
  6      WHERE Q.EDATE = U.EDATE + 1
  7        AND Q.USERID_2||Q.B_C||Q.B_AO||Q.B_NUM||Q.T_C||Q.AO||Q.T_NUM
  8          = U.USERID_2||U.B_C||U.B_AO||U.B_NUM||U.T_C||U.AO||U.T_NUM)
  9  /

Let us know how these suggestions work for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LKBrwnDBA]

You did not copy and paste correctly the SQL I posted.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[acct098]

SQL> UPDATE SUSF u SET CLEARED = 'N'
2 WHERE EDATE IN ('01-DEC-06' , '30-NOV-06' )
3 AND EXIST (
4 SELECT '?' FROM SUSF q
5 WHERE q.EDATE = u.EDATE + 1
6 AND q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
7 = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)
8 /
SELECT '?' FROM SUSF q
*
ERROR at line 4:
ORA-00936: missing expression

 

[SantaMufasa]

LK's code needs to use the operator, EXISTS, instead of the syntactically incorrect EXIST.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[acct098]

After messing with the code it looks like I am onto something. I did not want to update the whole table so I adde the edates. I have remove them and currently running the following code:

UPDATE SUSF u SET CLEARED = 'N'
where EXISTs (
SELECT q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM FROM SUSF q
WHERE q.EDATE = u.EDATE + 1
AND q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
= u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)
/

I will let you know the results.

 

[SantaMufasa]

Also, Acct, for future use of the (very powerful) EXISTS operator, you need only SELECT some dummy (literal) expression for the code to work...you need not SELECT a meaningful expression as you have done, above. Therefore, given that everything else in your code works as you wanted, this variation is a satisfactory replacement:

UPDATE SUSF u SET CLEARED = 'N'
    where EXISTs (
   SELECT [b]'x'[/b] FROM SUSF q
    WHERE q.EDATE = u.EDATE + 1
      AND q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
        = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)
/

Let us know how things turn out.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

And to constrain on dates, try

UPDATE susf u SET cleared = 'N'
    WHERE u.edate IN ('30-NOV-06','1-DEC-06') AND
          EXISTS (SELECT 'x' FROM SUSF q
                   WHERE q.EDATE = u.EDATE + 1
                     AND q.USERID_2||q.B_C||q.B_AO||q.B_NUM||q.T_C||q.AO||q.T_NUM
        = u.USERID_2||u.B_C||u.B_AO||u.B_NUM||u.T_C||u.AO||u.T_NUM)
/

 

[acct098]

Does anyone know the syntax to show the runtime of a sql script?

 

[acct098]

Found it: Set timing on

 

[acct098]

I got it to work. It took a long time to run.

Is it possible to get the pecentage of the unfixed compared to the total from the the sql statement below (A)?


EDATE USERID_2 FIXED UNFIXED TOTAL
--------- -------- --------- --------- ---------
05-DEC-06 SXXXMRR 1 8 9
06-DEC-06 SXXXMRR 0 8 8

(A)
OL A HEADING "FIXED" FORMAT 9.99
COL B HEADING "UNFIXED" FORMAT 9.99
COL TOTAL HEADING "TOTAL" FORMAT 9.99


select EDATE, USERID_2,
COUNT(CASE WHEN CLEARED IS NULL
THEN 'X'
ELSE NULL END) A ,
COUNT(CASE WHEN CLEARED = 'N'
THEN 'X'
ELSE NULL END) B ,
COUNT(*) AS TOTAL
FROM SUSF
WHERE EDATE IN ('05-DEC-06' , '06-DEC-06')
AND USERID_2 = 'S796MRR'
GROUP BY USERID_2, EDATE
ORDER BY USERID_2, EDATE;

 

[carp]

Try this:

select EDATE, USERID_2,
   SUM(DECODE(CLEARED,NULL,1,0)) A,
   sum(DECODE(CLEARED,'N',1,0)) B,
   count(*) TOTAL,
   (SUM(DECODE(CLEARED,NULL,1,0))/count(*))*100 PCT_null
FROM susf
WHERE  EDATE IN ('05-DEC-06' , '06-DEC-06')
AND USERID_2 = 'S796MRR'
GROUP BY USERID_2, EDATE
ORDER BY USERID_2, EDATE;