July 29, 2008 at 12:32 pm
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!
July 29, 2008 at 1:49 pm
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.
July 30, 2008 at 11:36 am
awesome! Thanks
July 30, 2008 at 12:01 pm
So does that mean it worked for you?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 30, 2008 at 12:08 pm
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