SQL Modify 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.

---

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

---

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.

---

Replace

WHERE customerNo = 

with

WHERE customerNo IN

---

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