CODEWITHSUNDEEP
mysql
wellUKnow
wellUKnow

Reputation: 29

Mysql Unknown column on update

I am trying to update a column in the database but keep getting a error.

The error ERROR 1054 (42S22): Unknown column 'TT' in 'field list'

i have 2 tables users_main && users_info i need to set the value of users_info.JoinDate to users_main.created_date.

the query

UPDATE users_main AS Um, (
    SELECT ua.JoinDate
    FROM users_main AS um2
    LEFT JOIN  users_info
        AS ua ON um2.ID = ua.UserID) AS TT
SET Um.created_date = TT LIMIT 40;

Output from the subQuery

+---------------------+
| JoinDate            |
+---------------------+
| 2018-01-30 12:30:53 |
| 2018-01-30 13:30:37 |
| 2018-01-30 14:31:47 |
| 2018-01-30 15:04:40 |
| 2018-01-31 02:23:55 |
| 2018-02-02 09:43:59 |
| 2018-02-02 09:50:48 |
| 2018-02-02 09:55:09 |
| 2018-02-02 15:10:31 |
| 2018-02-02 18:43:49 |
+---------------------+

Upvotes: 0

Views: 72

Answers (1)

Barmar
Barmar

Reputation: 781974

SET Um.created_date = TT should be SET Um.created_date = TT.JoinDate.

But don't need the subquery, just a regular join between the two tables.

UPDATE users_Main as um
LEFT JOIN users_info AS ui ON um.ID = ui.UserID
SET um.created_date = ui.JoinDate
LIMIT 40

Note that LEFT JOIN will set created_date to NULL if there's no matching row in users_info. If you want to leave these rows alone, use INNER JOIN.

Upvotes: 3

Related Questions