SQL bulk update, a field increment

Question

So i learned how to do a bulk insert using data from one table as the userId from another table. Now i tried to do the same thing but i have a SQL(ite) error. I took a guess at the syntax and i got it wrong. After i bulk insert into the subscription i want to add 1 to each of the users media count. I have a left join error. How do i correct it?

-edit- I haven't solve this yet. Please help.

NOTE: I am using sqlite ATM but I am switching to mysql or MS sql

void updateMediaForSubscribers(long userId, long mediaId, Media_Base.Catagory cat, DateTime currentDate)
{
    command.CommandText =
        "INSERT INTO user_media_subscription (recipientId, mediaId, catagory) " +
        "SELECT watcher, @mediaId, @category " +
        "FROM user_watch WHERE watched=@watched;";
    command.Parameters.Add("@mediaId", DbType.Int64).Value = mediaId;
    command.Parameters.Add("@category", DbType.Int64).Value = cat;
    command.Parameters.Add("@watched", DbType.Int64).Value = userId;
    command.ExecuteNonQuery();

    //near "LEFT": syntax error
    command.CommandText =
        "UPDATE user_data SET mediaMsgCount=mediaMsgCount+1 " +
        "LEFT JOIN user_watch AS w ON w.watcher=user_data.userId " +
        "WHERE w.watched=@watched;";
    command.Parameters.Add("@watched", DbType.Int64).Value = userId;
    command.ExecuteNonQuery();
}

Answer 1

UPDATE user_data 
SET mediaMsgCount = u.mediaMsgCount + 1 
FROM user_data u LEFT OUTER JOIN user_watch w ON w.watcher = u.userId 
WHERE w.watched=@watched

Honestly, I'm not really sure why you're using a left outer join since the update is dependant on user_watch... might want to change the "LEFT OUTER JOIN" to an "INNER JOIN".

Also, its a good idea to prefix your table name with the owner, I.E. dbo.user_data ... if you dont do that then sql server has to do a lookup.

Answer 2

UPDATE user_data 
SET mediaMsgCount=mediaMsgCount+1
LEFT JOIN user_watch AS w ON w.watcher=user_data.userId
WHERE w.watched=@watched

Looks like it won't work to me, because you're joining in an UPDATE query but without a "FROM" clause, have you tried:

UPDATE u
SET u.mediaMsgCount = u.mediaMsgCount+1
FROM user_data u
LEFT JOIN user_watch AS w ON w.watcher=u.userId
WHERE w.watched=@watched