CODEWITHSUNDEEP
mysqlselectsql-update
khaverim
khaverim

Reputation: 3544

Intermediate MySQL query: updating table column based on separate table column value

I have a table ticketdetails which contains a column with data that I want to copy to another table's column:

SELECT TicketID, MIN( StartTime ) StartTime
FROM ticketdetails
GROUP BY TicketID #might be unnecessary?
ORDER BY TicketID

The output looks like this:

enter image description here

I want the result of this query, "StartTime", to be supplied in my other table column, tickets.TimeScheduled, where the TicketID matches for both tables.

Right now I'm trying:

UPDATE tickets SET TimeScheduled = (
SELECT MIN( StartTime ) StartTime
FROM ticketdetails
GROUP BY TicketID
ORDER BY TicketID)
WHERE tickets.TicketID = ticketdetails.TicketID

I get this error: 

#1054 - Unknown column 'ticketdetails.TicketID' in 'where clause'

I know that this column exists in my database. For some reason the query doesn't recognize the column. I think I need a JOIN or something, but I'm not the best with intermediate-advanced MySQL queries. Help is much appreciated.

Upvotes: 0

Views: 367

Answers (2)

Barmar
Barmar

Reputation: 781096

Use a join:

UPDATE tickets AS t
JOIN (SELECT TicketID, MIN( StartTime ) StartTime
      FROM ticketdetails
      GROUP BY TicketID) AS d ON t.TicketID = d.TicketID
SET t.TimeScheduled = d.StartTime

The reason you're getting an Unknown column error is because the ticketdetails table only exists in the subquery, not in the main query.

If you want to set timeScheduled to NULL if there's no matching ticket in ticketdetails, change JOIN to LEFT JOIN.

BTW, there's no need for ORDER BY in the subquery.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this with a correlated subquery:

UPDATE tickets
    SET TimeScheduled = (SELECT MIN( td.StartTime ) StartTime
                         FROM ticketdetails td
                         WHERE td.TicketID = t.TicketID
                        );

Upvotes: 0

Related Questions