Editing group entries via SQL - possible?

  • Florian


    I was wondering whether it is possible to edit only the entries that are aggregated in a user_group via an SQL query?

    I intended to use this as follows:

    create an entry in the "notes" field of all the citations listed in one user-group.

    problems I'm having so far:

    it does not seem possible to batch-edit citations in my groups via SQL (I need admin rights for that)
    if I log on as admin, I cannot access the user_groups anymore?

    Is that correct? Am I missing something?

    Thanks a lot for you help and time.

  • I think the admin has access to user_groups of other users (if he knows the user id).  So, this should be possible via UPDATE+JOIN from the admin account.


  • Florian


    thanks for the quick reply. That would be awesome if it could work. Because, following idea: Creating RSS-feeds from user_groups.
    My plan is to UPDATE all refs that are part of a user_group and edit them by filling a public-field in the reference which we do not use ("area" for example) with a specific tag (the name of the user_group). Now, the user can log on as admin, create the SQL query and later on create a RSS-feed containing all the references which have the group-name in the "area" field. Of course, this does not update itself, meaning the user would have to rerun the SQL-query everytime he/she adds new references to the group.

    So, since I don't know much (yet) about SQL, just the stuff I read on your documentation, I came up with this idea:

    UPDATE refs SET area = "MCN1" LEFT JOIN user_id = 4 WHERE user_groups RLIKE "MCN1" ORDER BY year DESC, author

    "MCN1" is the name of the user_group
    user_id 4 is my user ID in our repository.

    I don't think that this is correct code, I did not have time to look up the LEFT JOIN stuff in the documentation.

    Thanks so much again,

    will get back soon

  • It helps to test this in a good SQL client.  But, you'd want something like:

    UPDATE refs JOIN user_data ON refs.serial=user_data.record_id SET refs.area = "MCN1" where user_data.user_id = 4 AND user_data.user_groups RLIKE "MCN1";