Some help with Oracle, simple script i can't get to behave

B@Th*nG · 1 May 2015 at 11:03

Having difficultly removing the newest records from a table. I've probably gone about it the wrong way in the first instance but I've made it this far

I've created a temporary table, inserted the records (entityid, addressid and startdate of the address) and then just want to remove the newest record for each EntID.

What i want to do is pick out the AddressID's with the newest date and remove them, but i'm getting an error because the addID is not in the select statement:

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

Code:

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert BLAH BLAH

DELETE FROM ENTADD
WHERE AddID in
	(SELECT MAX(StDate)
	FROM ENTADD
	GROUP BY EntID);

any ideas?

B@

tom_e · 1 May 2015 at 11:08

Try putting the AddID in the sub query or else how it is going to match it with the "AddID in" as AddID isn't returned.

B@Th*nG · 1 May 2015 at 11:25

i had tried that, get this error instead:

DELETE FROM ENTADD
WHERE AddID in
(SELECT MAX(StDate), AddID
FROM ENTADD
GROUP BY EntID);

Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"
*Cause:
*Action:

B@

tom_e · 1 May 2015 at 11:30

You'll need to add it in the group by statement as well, as it is not a calculation.

mark66 · 1 May 2015 at 11:31

Is it not because your sub query is returning a date and your are trying to match that to the numeric AddID?

I would have thought you want this:

Code:

DELETE FROM ENTADD
WHERE StDate in
	(SELECT MAX(StDate)
	FROM ENTADD
	GROUP BY EntID);

B@Th*nG · 1 May 2015 at 11:34

tom_e said:
You'll need to add it in the group by statement as well, as it is not a calculation.

If i group by date or addressID then it won't pick the highest date grouped by entityID so it won't work will it?

An entityID has multiple addressID's, each with a startdate. I need the newest address ID per entityID deleted.

mark66 said:
Is it not because your sub query is returning a date and your are trying to match that to the numeric AddID?

I would have thought you want this:

Code:

DELETE FROM ENTADD WHERE StDate in (SELECT MAX(StDate) FROM ENTADD GROUP BY EntID);

That'll just delete any entries with those dates though. The unique identifier is either a combination of entityid AND date or just addressid

B@

kkelly · 1 May 2015 at 11:39

DROP TABLE ENTADD;

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (1,2, sysdate+1);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (2,2, sysdate+2);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (3,2, sysdate+3);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (4,2, sysdate+4);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,3, sysdate);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,2, sysdate+5);

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.addid = '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE addid = a.addid)
);

?

mark66 · 1 May 2015 at 11:40

B@Th*nG said:
...The unique identifier is either a combination of entityid AND date or just addressid

B@

Yeah, had spotted that after I posted it. Try this:

Code:

DELETE FROM ENTADD A 
WHERE A.StDate in (SELECT MAX(B.StDate)
                         FROM ENTADD B
                         WHERE A.EntID = B.EntID
                         GROUP BY B.EntID);

B@Th*nG · 1 May 2015 at 11:42

kkelly said:
DROP TABLE ENTADD;

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (1,2, sysdate+1);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (2,2, sysdate+2);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (3,2, sysdate+3);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (4,2, sysdate+4);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,3, sysdate);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,2, sysdate+5);

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.addid = '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE addid = a.addid)
);

?

Will try that out

mark66 said:
Yeah, had spotted that after I posted it. Try this:

Code:

DELETE FROM ENTADD A WHERE A.StDate in (SELECT MAX(B.StDate) FROM ENTADD B WHERE A.EntID = B.EntID GROUP BY B.EntID);

that'll still delete anything which matches that startdate, irrespective of what entityID it is, because you're just deleting anything with that startdate.

B@

B@Th*nG · 1 May 2015 at 11:47

kkelly said:
DROP TABLE ENTADD;

CREATE TABLE ENTADD(EntID number(10), AddID number(10), StDate date);

Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (1,2, sysdate+1);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (2,2, sysdate+2);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (3,2, sysdate+3);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (4,2, sysdate+4);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,3, sysdate);
Insert INTO ENTADD (EntID, ADDID, stdate) VALUES (5,2, sysdate+5);

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.addid = '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE addid = a.addid)
);

?

that's not going to remove the newest address from each entityID i dont think

B@

kkelly · 1 May 2015 at 11:49

Will remove the newest addis, replace addid with entid?

B@Th*nG · 1 May 2015 at 11:54

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.EntID= '2'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID)
);

0 rows deleted, what've i done wrong?

B@

kkelly · 1 May 2015 at 12:00

Did you run the insert, looks fine on my phone

Failing that mail me on trust and I'll do it when I get home

B@Th*nG · 1 May 2015 at 12:16

definitely data in the table:

thanks for your help!

B@

kkelly · 1 May 2015 at 12:41

hi,

Try

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.EntID= '268545'
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID)
);

Then do

Select * FROM entadd a
WHERE a.EntID= '268545';

You should only see the entry with the date of 01-Jan-01, is this what your trying to do for the full table?

B@Th*nG · 1 May 2015 at 13:33

yep, that's worked on that record

so i tried the below and got another error:

DELETE FROM ENTADD
WHERE rowid = (
SELECT rowid
FROM entadd a
WHERE a.EntID = EntID
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID)
)
Error report -
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

B@

kkelly · 1 May 2015 at 14:04

DELETE FROM entadd a
WHERE EntID IN
(
SELECT entid
FROM entadd
GROUP BY entid
HAVING COUNT(*) > 1
)
AND a.stdate = (
SELECT MAX(stdate)
FROM
entadd
WHERE EntID = a.EntID
);

B@Th*nG · 1 May 2015 at 14:37

thank you, thank you, thank you! perfect!

B@

kkelly · 1 May 2015 at 14:54

Heh, took me a while to understand you're problem, email on trust if you get stuck I'll try get back to you