Need help to INNER JOIN multiple tables and use last value row in MySQL

Question

I have 3 tables as follow:

comments

|id| |uid| |tid|

tracks

|id| |uid|

notifications

|id| |from| |tox|

How can I UPDATE notifications and SET tox as equal to the tracks.uid of its relative tracks.id that is equal to the last comments.tid value?

I tried this without success:

UPDATE notifications SET tox = (
  SELECT uid FROM tracks 
  INNER JOIN comments ON tracks.id = comments.tid ORDER BY comments.tid DESC LIMIT 1
  WHERE comments.tid=tracks.id)
WHERE tox = 0 ORDER BY id DESC LIMIT 1;

UPDATE

First I edit and moved the ORDER BY at the end as suggested. After that I got a different error 1052 - Column 'typeid' in field list is ambiguous.

I solved it like:

UPDATE `notifications` SET `tox` = (
  SELECT tracks.uid FROM `tracks` 
  INNER JOIN `comments` ON tracks.id = comments.tid 
  ORDER BY comments.id DESC LIMIT 1)
WHERE `tox` = 0 ORDER BY `id` DESC LIMIT 1;

possible duplicate of MySQL Inner Join Query Multiple Tables — Identity1
– Identity1, Commented Jun 25, 2015 at 12:10
You are updated tox column which reference of other table. How identify of dependancy. — Mukesh Kalgude
– Mukesh Kalgude, Commented Jun 25, 2015 at 12:16
@MukeshKalgude everything starts from last row of comments where the tid value is = to the tracks.id related to the tracks.uid that is what I need. tracks.id is a primary key as comments.id and notifications.id — NineCattoRules
– NineCattoRules, Commented Jun 25, 2015 at 12:24

Gordon Linoff · Accepted Answer · 2015-06-25 12:16:25Z

1

I think your problem is the syntax of the subquery:

UPDATE notifications
    SET tox = (SELECT uid
               FROM tracks INNER JOIN
                    comments
                    ON tracks.id = comments.tid
               WHERE comments.tid=tracks.id
               ORDER BY comments.tid DESC
               LIMIT 1
              )
    WHERE tox = 0
    ORDER BY id DESC
    LIMIT 1;

The ORDER BY always goes after the WHERE.

answered Jun 25, 2015 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NineCattoRules Over a year ago

I get another error: #1052 - Column 'uid' in field list is ambiguous...should I write SELECT tracks.uid instead?

NineCattoRules Over a year ago

yes it works now! I update my question with the final code

Collectives™ on Stack Overflow

Need help to INNER JOIN multiple tables and use last value row in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related