#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Update table from query


    I have a query (MySQL) which pulls data from 4 tables within the same database. What I would like to do is run the query and have the results be either updated if there was a change and have new records be inserted into a separate table in another database.

    Code:
        SELECT a.Created,
        a.id 'TicketID',
        GROUP_CONCAT((CASE WHEN d.CustomField = 1 THEN d.Content ELSE NULL END)) `CompanyName`,
        a.Subject,
        c.Name Queue,
        b.Name 'Owner',
        a.`Status`,
        a.LastUpdated,
        GROUP_CONCAT((CASE WHEN d.CustomField = 4 THEN d.Content ELSE NULL END)) `Location`,
        a.TimeWorked 'TimeWorked',
        GROUP_CONCAT((CASE WHEN d.CustomField = 2 THEN d.Content ELSE NULL END)) `OverRide`,
        a.Resolved
        FROM    rt.Tickets a
            INNER JOIN rt.Users b
                ON a.owner = b.id
            INNER JOIN rt.Queues c
                ON a.queue = c.id
            INNER JOIN  rt.ObjectCustomFieldValues d
                ON a.id = d.ObjectID
        GROUP BY a.id
    The above query is pulls data from our ticketing system.

    I was able to initally insert the data using the following:

    Code:
        INSERT INTO Support (Created, TicketID, CompanyName, Subject, Queue, Owner, Status, LastUpdated, Location, Timeworked, OverRide, Resolved)
        SELECT a.Created,
        a.id 'TicketID',
        GROUP_CONCAT((CASE WHEN d.CustomField = 1 THEN d.Content ELSE NULL END)) `CompanyName`,
        a.Subject,
        c.Name Queue,
        b.Name 'Owner',
        a.`Status`,
        a.LastUpdated,
        GROUP_CONCAT((CASE WHEN d.CustomField = 4 THEN d.Content ELSE NULL END)) `Location`,
        a.TimeWorked 'TimeWorked',
        GROUP_CONCAT((CASE WHEN d.CustomField = 2 THEN d.Content ELSE NULL END)) `OverRide`,
        a.Resolved
        FROM    rt.Tickets a
            INNER JOIN rt.Users b
                ON a.owner = b.id
            INNER JOIN rt.Queues c
                ON a.queue = c.id
            INNER JOIN  rt.ObjectCustomFieldValues d
                ON a.id = d.ObjectID
        GROUP BY a.id
    However when trying to update the data that is already there or adding additional new data I get errors.

    Code:
        UPDATE Support
        (SELECT a.Created,
        a.id 'TicketID',
        GROUP_CONCAT((CASE WHEN d.CustomField = 1 THEN d.Content ELSE NULL END)) `CompanyName`,
        a.Subject,
        c.Name Queue,
        b.Name 'Owner',
        a.`Status`,
        a.LastUpdated,
        GROUP_CONCAT((CASE WHEN d.CustomField = 4 THEN d.Content ELSE NULL END)) `Location`,
        a.TimeWorked 'TimeWorked',
        GROUP_CONCAT((CASE WHEN d.CustomField = 2 THEN d.Content ELSE NULL END)) `OverRide`,
        a.Resolved
        FROM    rt.Tickets a
            INNER JOIN rt.Users b
                ON a.owner = b.id
            INNER JOIN rt.Queues c
                ON a.queue = c.id
            INNER JOIN  rt.ObjectCustomFieldValues d
                ON a.id = d.ObjectID
        GROUP BY a.id)
    I am not sure that is the issue is on the update query.
    Thanks,
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,013
    Rep Power
    534
    I don't think what you are attempting to do is possible using just SQL. If the same database, check out http://dev.mysql.com/doc/refman/5.0/...duplicate.html. If you want it to write to another database, check if it exists in the first one (either select first and re-direct or use try/catch), and then write in the second database.
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Doing a replace query instead of an insert/update might work.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10

    You can submit


    You can submit to a different database table you just have to access the server completely.

    More importantly it sounds like what you want to do requires a trigger.

IMN logo majestic logo threadwatch logo seochat tools logo