Query

I have duplicate records in a table based on say
OrderNum, I would like to copy 1st record for each
OrderNum from this table into a new table and then update
the OrderNum record in the 2nd table with values from
rest of the records in the 1st table, can someone help me
in doing this?
Ex.
Frist table may have -
ORder1 AreaField1 description ...
ORder1 AreaField1 Description ...
Order1 AreaField2 Description ...
ORder2 AreadField Description ...
Order2 AreaField Description ...
ORder3 AreaField Description ...
ORder4 AreaField Description ...
I would like the 2nd table to look lik -
ORder1 AreaField1+AreaField2 Description ...
ORder2 AreaField Description ...
Order3 AreaField Description ...
ORder4 AreaField Description...
Thank you very much,
-Kim
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:7748
On Thu, 12 Aug 2004 11:56:32 -0700, Kim wrote:
(snip)
Hi Kim,
I just replied to an equal post in .programming.
Please don't multi-post!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query

hey,
i have two tables that I want to match, the records that match I want to delete them from the original file and copy them in an other Tabl
Example
Table A Table
1A 3
3B 4
5J 1
All the records that match in that example (1A and 3B), I want to copy them in a new table and delete them only form TableB
I made a join between the two tables, and I have good results. But how to copy and delete them I don't know
Or maybe in Table B I can have a flag that I put on when it is a mached record? But that I nether don't know how to do that in my join query
Thks for help
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jacsql

Query

hey,
i have two tables that I want to match, the records that match I want to del
ete them from the original file and copy them in an other Table
Example:
Table A Table B
1A 3B
3B 4V
5J 1A
All the records that match in that example (1A and 3B), I want to copy them
in a new table and delete them only form TableB.
I made a join between the two tables, and I have good results. But how to co
py and delete them I don't know?
Or maybe in Table B I can have a flag that I put on when it is a mached reco
rd? But that I nether don't know how to do that in my join query.
Thks for help.
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jac

QA tells me my table is ambiguous

Can someone help with this syntax? I have a non-sensicle example
below, but it illustrates the problem if you copy/paste into QA.

**********************************

use pubs
go

update authors set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

--------------

Server: Msg 8154, Level 16, State 1, Line 2
The table 'authors' is ambiguous.

**********************************It needs to know which alias to update a or a2.

update a set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

Jackie

<john.livermore@.inginix.com> wrote in message
news:1114025028.974990.86780@.o13g2000cwo.googlegro ups.com...
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
> **********************************
> use pubs
> go
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
> --------------
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
>
> **********************************|||thx!|||Of course, instead of the Microsoft proprietary syntax, you could also
write this statement with the ANSI SQL compliant syntax, as follows:

-- Note: The update is still non-sensicle...
UPDATE Authors
SET Address = (
SELECT 'some address'
FROM Authors A2
WHERE A2.zip = Authors.zip
)

HTH,
Gert-Jan

john.livermore@.inginix.com wrote:
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
> **********************************
> use pubs
> go
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
> --------------
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
> **********************************