Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
GeorgeOne thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George