Please help updating ID field

  • I have a table where I keep URL links from Google. A user will review each one and decide if it should be kept or deleted. If deleted, the entire record is deleted. If kept, the user has to enter a few pieces of information, such as "quality" and "category", etc.

    Once that's been done, I need to update another field, the unique documentID. This is made up of the customer ID field from another table, and the datetime stamp of when the URL was received. I can get this SQL part fine, see below. But I need help inserting it into the documentID field.

    It's almost like I need my below select query with an UPDATE query after it to insert the value returned

    SELECT Google_Alerts.ID,

    CONVERT(varchar(2), (mytable_clients.ID)) +

    CONVERT(varchar(4), DATEPART([year], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([month], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([day], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([hour], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([minute], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([second], Google_Alerts.Received)) AS DocumentID

    FROM mytable_clients INNER JOIN

    Google_Alerts ON mytable_clients.longName = Google_Alerts.Client

    WHERE Google_Alerts.ID = 630

    -- then insert this returned value into another field:

    Update Google_Alerts set documentID = DocumentID, ID = [Value]

    Any help is greatly appreciated, thanks!

  • Not tested, but I think this will work. You might want to put a trigger on one of the fields that are required to be updated in order to keep the record.

    UPDATE Google_Alerts

    SET DocumentID = CONVERT(varchar(2), (mytable_clients.ID)) +

    CONVERT(varchar(4), DATEPART([year], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([month], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([day], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([hour], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([minute], Google_Alerts.Received)) +

    CONVERT(varchar(2), DATEPART([second], Google_Alerts.Received))

    FROM mytable_clients INNER JOIN Google_Alerts

    ON mytable_clients.longName = Google_Alerts.Client

    WHERE Google_Alerts.ID = 630

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • awesome! Thanks

  • So does that mean it worked for you?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Yes, it worked fine. I had to change the ID to a variable of the current ID, but the synatax was perfect. Thanks again.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply