phpmyadmin Question posted in
The official documentation can be found here.

UPDATE INNER JOIN unknown column in on clause – Phpmyadmin

I know there are already some answers to this question but none of them seem to work for me.

Basically, i’ve got tasks which are nothing more than presets sheduled over time : i want to update the tasks date when reaching the deadlines ; also when i reach a deadline, i need to update the values of the components table according to the preset values.

I successfully made the query to update the deadlines which looks like this :

UPDATE tasks

    SET tasks.start_date = DATE_ADD(
    tasks.start_date, INTERVAL tasks.frequency SECOND
    )

    WHERE tasks.start_date <= CURRENT_DATE AND tasks.hour <= CURRENT_TIME

When I try update the components rows at the same time as the deadlines i can’t get it to work. My query looks like this :

    UPDATE tasks,components 

    INNER JOIN presets ON presets.id = tasks.id_preset
    INNER JOIN preset_values ON preset_values.id_preset = presets.id
    INNER JOIN preset_values AS pv2 ON pv2.serial_number = 
    components.serial_number 

    SET tasks.start_date = DATE_ADD(
    tasks.start_date, INTERVAL tasks.frequency SECOND
    ),
    components.value = pv2.value,
    components.state = pv2.on_off

    WHERE tasks.start_date <= CURRENT_DATE AND tasks.hour <= CURRENT_TIME

The message error I get is :
MySQL said: Documentation

1054 – Unknown column ‘tasks.id_preset’ in ‘on clause’

can you help me to get this query to work ? thank you

components table : https://i.stack.imgur.com/VOZX8.png

presets table : https://i.stack.imgur.com/Fs7yO.png

preset_values table : https://i.stack.imgur.com/AZkcm.png

tasks table : https://i.stack.imgur.com/0qSEe.png

EDIT : after thinking twice I opted for two separate update queries. It currently looks like this. If anyone has a single update solution I’ll be glad to use it if it’s better performance wise. Otherwise I’ll stick to this solution for the moment. Thank you all for your kind help.

BEGIN
    UPDATE
        components AS c1
    INNER JOIN preset_values AS pv1
    ON
        pv1.serial_number = c1.serial_number
    INNER JOIN tasks AS t1
    ON
        t1.id_preset = pv1.id_preset
    SET
        c1.value = pv1.value,
        c1.state = pv1.on_off
    WHERE
        t1.start_date <= CURRENT_DATE AND t1.hour <= CURRENT_TIME AND t1.on_off=1;
    UPDATE
        tasks
    SET
        tasks.start_date = DATE_ADD(
            tasks.start_date,
            INTERVAL tasks.frequency SECOND
        )
    WHERE
        tasks.start_date <= CURRENT_DATE AND tasks.hour <= CURRENT_TIME AND t1.on_off=1 ;
END
Tags:

2

Answers


  1. 0

    you need to connect tasks and components on where clause. now, they are cross joined.

    Login or Signup to reply.
  2. 0

    If you really do need a cross join for some reason (extremely unlikely), you should use the CROSS JOIN clause or swap the comma-separated tables into components, tasks. Your query

    UPDATE tasks,components INNER JOIN presets ON presets.id = tasks.id_preset ...

    means

    UPDATE tasks, (components INNER JOIN presets ON presets.id = tasks.id_preset) ...

    Obviously there is no tasks.id_preset in components INNER JOIN presets ON presets.id = tasks.id_preset

    However, a cross join would mean that each of your filtered explicit joined rows are combined with each row of the table components. The last each row would perform a

    SET
components.value = pv2.value,
components.state = pv2.on_off

    I do not believe that this is anyhow intended. Think twice if this operation on the Cartesian product is actually what you want.

    There is a serial_number in components, so you most likely want

    UPDATE     tasks
INNER JOIN presets       ON presets.id               = tasks.id_preset
INNER JOIN preset_values ON preset_values.id_preset  = presets.id
INNER_JOIN components    ON components.serial_number = preset_values.serial_number 

SET ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search