c*********n
发帖数: 128 | 1 I am wondering if there is a qry can the following job:
Let's say if we have two tables, TBL_1, TBL_2 with the same structure:
field_x, field_y.
Values in field_x are supposed to be unique.
I want to merge TBL_2 into TBL_1, that is,
1) for whatever value x existing in both TBL_2.field_x and
TBL_1.field_x, update the filed_y of records in TBL_1 to
TBL_2.field_y.
2) for whatever value x existing in TBL_2 but not in TBL_1, append
these records to TBL_1.
Currently I implement this purpose using 3 qu |
h******l
发帖数: 422 | 2 MERGE INTO TBL_1 AS Target
USING (SELECT field_x, field_y from TBL_2 ) AS Source (x2, y2)
ON Target.field_x = Source.x2
WHEN MATCHED THEN
UPDATE SET Target.field_y = Source.y2
WHEN NOT MATCHED BY TARGET THEN
INSERT (field_x, field_y) VALUES (x2, y2)
【在 c*********n 的大作中提到】
: I am wondering if there is a qry can the following job:
: Let's say if we have two tables, TBL_1, TBL_2 with the same structure:
: field_x, field_y.
: Values in field_x are supposed to be unique.
: I want to merge TBL_2 into TBL_1, that is,
: 1) for whatever value x existing in both TBL_2.field_x and
: TBL_1.field_x, update the filed_y of records in TBL_1 to
: TBL_2.field_y.
: 2) for whatever value x existing in TBL_2 but not in TBL_1, append
: these records to TBL_1.
|
c*********n
发帖数: 128 | 3 Sorry I forgot to mention I was working on Microsoft Access, so not every
SQL sentence works there.
MERGE seems to be one of those SQL phrases unavailable on Microsoft Access..
....
【在 h******l 的大作中提到】
: MERGE INTO TBL_1 AS Target
: USING (SELECT field_x, field_y from TBL_2 ) AS Source (x2, y2)
: ON Target.field_x = Source.x2
: WHEN MATCHED THEN
: UPDATE SET Target.field_y = Source.y2
: WHEN NOT MATCHED BY TARGET THEN
: INSERT (field_x, field_y) VALUES (x2, y2)
|
h******l
发帖数: 422 | 4 You can create a view to mimic the MERGE. I don't think Access has anything
to physically Merge (Update/Insert) like full Sql server. I'd suggest to
migrate to SQL Server 2008 Express if you want to do any advanced SQL. :)
select b.field_x, b.field_y
from TBL_2 b left join TBL_1 a
on b.field_x = a.field_x
UNION ALL
select a.field_x, a.field_y
from TBL_1 a
where not exists (select 1 from TBL_2 b where a.field_x = b.field_x)
..
【在 c*********n 的大作中提到】
: Sorry I forgot to mention I was working on Microsoft Access, so not every
: SQL sentence works there.
: MERGE seems to be one of those SQL phrases unavailable on Microsoft Access..
: ....
|
c*********n
发帖数: 128 | 5 Thanks for the reply!
So you mean there is no way to make a table containing the merged TBL_1 and
TBL_2?
I tried the following revised sentence (added "INTO TBL_MergeResult" in the
first line), but it didn't work and gave msg "An action query cannot be used
as a row source".
select b.field_x, b.field_y INTO TBL_MergeResult
from TBL_2 b left join TBL_1 a
on b.field_x = a.field_x
UNION ALL
select a.field_x, a.field_y
from TBL_1 a
where not exists (select 1 from TBL_2 b where a.field_x = b.field_x)
【在 h******l 的大作中提到】
: You can create a view to mimic the MERGE. I don't think Access has anything
: to physically Merge (Update/Insert) like full Sql server. I'd suggest to
: migrate to SQL Server 2008 Express if you want to do any advanced SQL. :)
: select b.field_x, b.field_y
: from TBL_2 b left join TBL_1 a
: on b.field_x = a.field_x
: UNION ALL
: select a.field_x, a.field_y
: from TBL_1 a
: where not exists (select 1 from TBL_2 b where a.field_x = b.field_x)
|
h******l
发帖数: 422 | 6 Yes, you can, you must create Access stored procedure that involves two
steps update and insert on TBL_1.
and
the
used
【在 c*********n 的大作中提到】
: Thanks for the reply!
: So you mean there is no way to make a table containing the merged TBL_1 and
: TBL_2?
: I tried the following revised sentence (added "INTO TBL_MergeResult" in the
: first line), but it didn't work and gave msg "An action query cannot be used
: as a row source".
: select b.field_x, b.field_y INTO TBL_MergeResult
: from TBL_2 b left join TBL_1 a
: on b.field_x = a.field_x
: UNION ALL
|
c*********n
发帖数: 128 | 7 But no way through query?
I tried adding "INTO TBL_MergeResult" to the first line of your code (see my
previous reply) but it did not work.
【在 h******l 的大作中提到】
: Yes, you can, you must create Access stored procedure that involves two
: steps update and insert on TBL_1.
:
: and
: the
: used
|
h******l
发帖数: 422 | 8 Try
SELECT ...... INTO TBL_MERGERESULT
What is your purpose of using this query? Why can't you do Update then
Insert? Can you please specify your problem so I can help you?
my
【在 c*********n 的大作中提到】
: But no way through query?
: I tried adding "INTO TBL_MergeResult" to the first line of your code (see my
: previous reply) but it did not work.
|
|
B*****g
发帖数: 34098 | 9 select *
into TBL_MergeResult
from (
select b.field_x, b.field_y
from TBL_2 b left join TBL_1 a
on b.field_x = a.field_x
UNION ALL
select a.field_x, a.field_y
from TBL_1 a
where not exists (select 1 from TBL_2 b where a.field_x = b.field_x))
and
the
used
【在 c*********n 的大作中提到】
: Thanks for the reply!
: So you mean there is no way to make a table containing the merged TBL_1 and
: TBL_2?
: I tried the following revised sentence (added "INTO TBL_MergeResult" in the
: first line), but it didn't work and gave msg "An action query cannot be used
: as a row source".
: select b.field_x, b.field_y INTO TBL_MergeResult
: from TBL_2 b left join TBL_1 a
: on b.field_x = a.field_x
: UNION ALL
|
c*********n
发帖数: 128 | 10 That works! Thanks so much!
My purpose is to merge (update existing and insert new) table2 into table1.
I did the job using the three queries in the first post but thought it's
kinda stupid to do it that way.
【在 h******l 的大作中提到】
: Try
: SELECT ...... INTO TBL_MERGERESULT
: What is your purpose of using this query? Why can't you do Update then
: Insert? Can you please specify your problem so I can help you?
:
: my
|
c*********n
发帖数: 128 | 11 It works! Thanks a lot!
【在 B*****g 的大作中提到】
: select *
: into TBL_MergeResult
: from (
: select b.field_x, b.field_y
: from TBL_2 b left join TBL_1 a
: on b.field_x = a.field_x
: UNION ALL
: select a.field_x, a.field_y
: from TBL_1 a
: where not exists (select 1 from TBL_2 b where a.field_x = b.field_x))
|
z********n
发帖数: 710 | |
