August 25, 2011 at 9:33 am
Hi,
I have the selection criteria for a group of records that I need to update below(1st section), what I need to do is update the field "iweb_conf_demo.Attendee_ribbons" on their records. When I include the "SET" statement( it updates every record in the database - how can I update just the records from the select query?
SELECT Name.ID, Name.FULL_NAME, Name.MEMBER_TYPE, Name.CATEGORY, Name_Demo.Training_Date
FROM (iMIS.dbo.ISGWEB_CONF_MTR_AGMT ISGWEB_CONF_MTR_AGMT INNER JOIN iMIS.dbo.Name Name ON ISGWEB_CONF_MTR_AGMT.ID=Name.ID)
INNER JOIN iMIS.dbo.Name_Demo Name_Demo ON ISGWEB_CONF_MTR_AGMT.ID=Name_Demo.ID
WHERE Name.MEMBER_TYPE='RES' AND (Name.CATEGORY='CAPF' OR Name.CATEGORY='POST' OR Name.CATEGORY='TB') AND Name_Demo.Training_Date>={ts '2010-12-31 00:00:01'}
UPDATE iweb_conf_demo
SET iweb_conf_demo.Attendee_ribbons = '0770'
Thanks! DJ
August 25, 2011 at 9:43 am
Put the same WHERE clause on your UPDATE statement as on your SELECT statment.
August 25, 2011 at 11:16 am
So it should read:
UPDATE iweb_conf_demo
SET iweb_conf_demo.Attendee_ribbons = '0770'
WHERE Name.ID, Name.FULL_NAME, Name.MEMBER_TYPE, Name.CATEGORY, Name_Demo.Training_Date
FROM (iMIS.dbo.ISGWEB_CONF_MTR_AGMT ISGWEB_CONF_MTR_AGMT INNER JOIN iMIS.dbo.Name Name ON ISGWEB_CONF_MTR_AGMT.ID=Name.ID)
INNER JOIN iMIS.dbo.Name_Demo Name_Demo ON ISGWEB_CONF_MTR_AGMT.ID=Name_Demo.ID
WHERE Name.MEMBER_TYPE='RES' AND (Name.CATEGORY='CAPF' OR Name.CATEGORY='POST' OR Name.CATEGORY='TB') AND Name_Demo.Training_Date>={ts '2010-12-31 00:00:01'}
August 25, 2011 at 12:01 pm
You've got a mess that looks like it came from Access. Here is the general format for an UPDATE statement:
UPDATE table1, table2, table3
SET table1.field1 = 'abc',
table1.field2 = 345
WHERE table1.id = table2.tbl1fk
AND table1.id = table3.tbl1fk
AND table1.MEMBER_TYPE = 'RES'
AND (table2.CATEGORY IN ('CAPF', 'POST', 'TB'))
AND table3.Training_Date >= '2010-12-31'
August 25, 2011 at 12:06 pm
LOL!
Forunately, it didn't come from access but I guess that shows I have a lot to learn about structure. 😀
Thanks for hanging in there with me.
DJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply