CODEWITHSUNDEEP
phpmysql
Hitu Bansal
Hitu Bansal

Reputation: 3137

UPDATE records with conditions in mysql

I have a following query 

SELECT count(Distinct user_bookmarks.user_id) AS bookmark_counter, bookmark_id 
FROM user_bookmarks 
LEFT JOIN bookmarks ON user_bookmarks.bookmark_id = bookmarks.id 
group by user_bookmarks.bookmark_id

which is working fine

Now I want to update records from bookmarks table 

UPDATE bookmark 
set bookmarks_counter = bookmark_counter from from above query 
where id = bookmark_id which is again from above query

from above query

Any idea?

Can we do this?

Thanks

Upvotes: 1

Views: 47

Answers (3)

Lex Eugene
Lex Eugene

Reputation: 15

You could try this: 

UPDATE dbo.bookmarks SET bookmarks_counter = result 
WHERE user_bookmarks.user_id 
IN (SELECT count(Distinct user_bookmarks.user_id)
    AS bookmark_counter, bookmark_id  FROM user_bookmarks 
    LEFT JOIN bookmarks ON user_bookmarks.bookmark_id =  bookmarks.id  
    GROUP BY user_bookmarks.bookmark_id)

Upvotes: 0

Mani
Mani

Reputation: 888

Try this one:

 UPDATE
   bookmark 
SET 
   bookmark.bookmarks_counter = other.bookmark_counter 

FROM
    (SELECT count(Distinct user_bookmarks.user_id) AS bookmark_counter, bookmark_id 
FROM user_bookmarks 
LEFT JOIN bookmarks ON user_bookmarks.bookmark_id = bookmarks.id 
group by user_bookmarks.bookmark_id) other
WHERE 
   bookmark.id = other.bookmark_id

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

you can write in a query in your update like so

UPDATE bookmark bk,
(   SELECT count(Distinct user_bookmarks.user_id) AS bookmark_counter, bookmark_id 
    FROM user_bookmarks 
    LEFT JOIN bookmarks ON user_bookmarks.bookmark_id = bookmarks.id 
    GROUP BY user_bookmarks.bookmark_id
) t
SET bk.bookmarks_counter = t.bookmark_counter
WHERE bk.id = t.bookmark_id

Upvotes: 2

Related Questions