CODEWITHSUNDEEP
mysqlsql
Anwar Saiah
Anwar Saiah

Reputation: 175

Need to update table based on results from another table

I need to update users table based on results from ads table, as follows:

if at least one column in ads is outdated(meaning duration in days from last time ad was edited has finished before today), then update "changed" column of the "users" table to 1 for the user owning the ad.

Here is my code that gives mysql error:

UPDATE users SET changed = IF(COUNT (ads.user_id) WHERE    
    (DATEDIFF()/ads.duration)>1 )>0, 1, 0) WHERE ads.user_id = users.id;

as you can see I am trying to set users.changed = 1 where ever at least one ad has expired from ads table belonging to this user. Note that if duration is -1 then this needs not be checked.

Upvotes: 0

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The correct way to approach this uses exists. The condition on the time is rather confusing, but I think you want:

update users
    set changed = 1
    where exists (select 1
                  from ads a
                  where a.user_id = u.uid and
                        a.last_edited < current_date - ads.duration day
                 );

In particular, this assures that each row is updated only once. When you use a JOIN in an update, you can have multiple matches. That can affect performance and lead to inconsistent results.

Upvotes: 1

Anwar Saiah
Anwar Saiah

Reputation: 175

This worked:

UPDATE users 
INNER JOIN ( 
    SELECT DISTINCT ads.user_id AS uid FROM ads 
    WHERE (DATEDIFF(NOW(),ads.last_edited)/ads.duration) > 1
           ) 
    ads 
ON users.id = uid 
SET users.changed=1

Upvotes: 0

Nae
Nae

Reputation: 15325

You need to join the ads table just after UPDATE statement.

You're probably trying to write the query:

UPDATE users
INNER JOIN (
    SELECT DISTINCT user_id FROM ads
    WHERE ( DATEDIFF(NOW(), last_edited) / duration > 1 )
    ) ads ON ads.user_id = users.id
SET changed = IF(COUNT (ads.user_id)
;

Upvotes: 1

Related Questions