SQL Modify question

Question

I need to replace a lot of values for a Table in SQL if the inactivity is greater then 30 days.

I have

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo = (SELECT c.VersionNo
                       FROM Activity b 
                      INNER JOIN VERSION c ON b.VersionNo = c.VersionNo
                      WHERE (Months_between(sysdate, b.Activitye) > 30));

It only works for one value though, if there is more then one returned it fails. What am I missing here?

If someone could educate me on what is going on, I'd also appreciate it.

score 3 · Answer 1 · answered Feb 19 '11 at 14:12

3

You use WHERE customerNo = (SELECT ...); This only works for 1 value. Try using WHERE customerNo IN (SELECT ...);

answered Feb 19 '11 at 14:12

teuneboon

4,034
5
23
28

the `IN (SELECT ...)` runs very slowly (see [this post](http://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values)). Better to use a join. – awm Feb 19 '11 at 14:17
@awn: when I look at this query I don't think it will be executed a lot. Looks like something that runs daily or so, so I'm explaining it with his own query here and not totally rewriting it. – teuneboon Feb 19 '11 at 14:22
@awn: That question is tagged MySQL -- this is for Oracle. – OMG Ponies Feb 20 '11 at 04:46

score 2 · Accepted Answer · answered Feb 20 '11 at 04:42

There are numerous ways of handling that a subquery returns more than one row. The issue to address is if the subquery returning multiple values is correct, or if it should only ever return one row. The equals operator against a subquery expects that only one row/record will be returned.

Also, MONTHS_BETWEEN returns the months between the two dates. If you want to see the number of days, use b.activity - SYSDATE > 30, or vice versa if appropriate.

Assuming more than one row is valid -

IN clause

There's no need for the 2nd join in the subquery to the VERSION table:

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo IN (SELECT c.customerno
                        FROM Activity b 
                       WHERE b.VersionNo = VERSION.VersionNo
                         AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

The VERSION table alias refers to the table being updated.

EXISTS clause

UPDATE VERSION 
   SET isActive = 0
 WHERE EXISTS(SELECT NULL
                FROM ACTIVITY a
               WHERE VERSION.customerno = a.customerno
                 AND VERSION.versionno = a.versionno
                 AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

There can be only one?

If only one row should be returned, the appropriate aggregate function (MIN, MAX) might be considered.

score 1 · Answer 3 · answered Feb 19 '11 at 14:11

1

Replace

WHERE customerNo =

with

WHERE customerNo IN

answered Feb 19 '11 at 14:11

Albin Sunnanbo

46,430
8
69
108

score 1 · Answer 4 · answered Feb 19 '11 at 15:19

MERGE INTO VERSION dest
USING (SELECT ver.VersionNo
FROM Activity act 
INNER JOIN VERSION ver ON act.VersionNo = var.VersionNo
WHERE (Months_between(sysdate, act.Activitye) > 30)) src
ON (scr.customerNo = dest.customerNo)
WHEN MATCHED THEN
UPDATE SET isActive = 0

SQL Modify question

4 Answers4

IN clause

EXISTS clause

There can be only one?