MYSQL insert into + select + CASE + delete not working

Question

I have 2 identical tables DATES and DATES_ARCHIVED

I want to select in the DATES table all the rows that have dates that are older than current date -88 days, then insert those rows into the DATES_ARCHIVED.

I have created a CASE for this.

Then after the insertion I want to delete those same rows from the original DATES table.

I am certain I got this to work before, but it's not working anymore it keeps giving error:

Error Code: 1136. Column count doesn't match value count at row 1

all the tables have the same amount of columns and I also removed the 2 triggers I had on the tables but the same error occurs.

I am pretty sure it's because of the case where it ends AS MostRecentDate, I think this is adding a new column.

Also if I remove all the code below the first: from dates, I get the same error

Here is the code:

INSERT INTO dates_archived

(term, course_no, date1_end, date2_end, date3_end, date4_end)

SELECT

term, course_no, date1_end, date2_end, date3_end, date4_end,

    CASE    WHEN date1_end > date2_end THEN date1_end
            WHEN date2_end > date1_end THEN date2_end
            WHEN date3_end > date1_end THEN date3_end
            WHEN date4_end > date1_end THEN date4_end

            WHEN date1_end > date3_end THEN date1_end
            WHEN date2_end > date3_end THEN date2_end
            WHEN date3_end > date2_end THEN date3_end
            WHEN date4_end > date2_end THEN date4_end

            WHEN date1_end > date4_end THEN date1_end
            WHEN date2_end > date4_end THEN date2_end
            WHEN date3_end > date4_end THEN date3_end
            WHEN date4_end > date3_end THEN date4_end

    ELSE 0

     END AS MostRecentDate

FROM dates

HAVING (MostRecentDate <= (SELECT CURDATE() - INTERVAL 88 DAY));

FROM dates

WHERE (date1_end OR date2_end OR date3_end OR date4_end = MostRecentDate);

DELETE FROM dates

WHERE (date1_end OR date2_end OR date3_end OR date4_end = MostRecentDate);

Thanks in advance, Chris

Also you can't use an alias you defined in the select clause in the where clause. — fancyPants, Aug 28 '17 at 08:46
And how should your delete statement know about MostRecentDate? — fancyPants, Aug 28 '17 at 08:47
I'm guessing that the CASE statement doesn't work. You should write `WHEN date1_end >= date2_end AND date1_end >= date3_end AND date1_end >= date4_end THEN date1_end` — Adder, Aug 28 '17 at 08:49

MirzaS · Accepted Answer · 2017-08-28T08:53:22.520

With your CASE you put a new row in there that's called MostRecentDate, so the row count doesn't match the table row count (since you're only using these term, course_no, date1_end, date2_end, date3_end, date4_end in the insert).

If you really need that field MostRecentDate you'd have to add it in the DATES_ARCHIVED table so the row count is the same and your statements will work perfectly fine.

If you don't need that row inserted into the table just delete the CASE statement from the code and it should work without giving you any errors.

Also in your code, you are using MostRecentDate in DELETE statement, but that variable should be deleted after the insert into statement is finished, so the code will break there. If you want to use it in the delete statement also you need to declare it again.

I'd suggest you make a variable and do the CASE statement to save the value inside it and just use that variable inside your HAVING / WHERE and DELETE, so that way you won't have to have it in the SELECT and the code won't break there either.

Yes I added MostRecentDate as well in dates_archived and it adds the rows but it won't delete them from dates. I will answer the comments above for the delete part. — PAPADOC, Aug 28 '17 at 08:52
I just edited the answer for delete also. Hope I managed to help you, have a good day sir. — MirzaS, Aug 28 '17 at 08:54

MYSQL insert into + select + CASE + delete not working

1 Answers1