June 29, 2009 at 9:50 am
Update Select Query Help
Hi,
I think I have a simple logic issue here. I want to update a field where the record_type = company but I only want one instance where a company name exists more than once. I know if i run the Select Query alone i get the results is need. When i put it with the update statement, every record in the database gets updated.
Any help would be great. Thanks.
Query i have been trying to get working...
UPDATE wce_contact SET pager = 'Parent' WHERE EXISTS (SELECT MAX(COMPANY) AS company
FROM wce_contact
WHERE (Record_Type = 'Company')
GROUP BY COMPANY)
This gets the results i need updated.
SELECT MAX(COMPANY) AS company
FROM wce_contact
WHERE (Record_Type = 'Company')
GROUP BY COMPANY
June 29, 2009 at 10:09 am
Your UPDATE statement is written to where it would update the entire wce_contact table if your sub-select returns a rowcount > 0.
Can you post your table DDL for wce_contact? How about some sample data as well?
In the meantime, you can try this code, but I'm not sure it is what you're after without seeing the table and some sample data.
UPDATE t1
SET pager = 'Parent'
FROM wce_contact t1
INNER JOIN (
SELECT MAX(Company) as Company
FROM wce_contact
WHERE Record_Type = 'Company'
GROUP BY Company
) t2
ON t1.Company = t2.Company
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply