Oprettelse af nye poster, baseret på eksisterende poster

Det skal vel være noget a la:

INSERT INTO tabel (owner_id,atype_idx,f1,f2,f3)
SELECT owner_id,1,f1,f2,f3 FROM tabel t1
WHERE NOT EXISTS (SELECT * FROM tabel t2 WHERE t1.owner_id=t2.owner_id AND t2.atype_idx=1)

http://www.eksperten.dk/spm/511502

INSERT INTO tbladresser ([owner_id], [atype_idx],[zipcode] ,[city] ,[County])

SELECT [owner_id], 1,[zipcode] ,[city] ,[County]
FROM tbladresser WHERE tbladresser.[atype_idx] = 2 AND [owner_id] NOT IN
(SELECT T.Owner_ID FROM tbladresser T WHERE [atype_idx] = 1 )

.

with refrenec to http://www.eksperten.dk/spm/511502

Not as it is! The table and field names need altering.
INSERT INTO tbladresser ([owner_id], [atype_idx],[zipcode] ,[city] ,[County])
SELECT owner_id, 1,[zipcode] ,[city] ,[County] FROM tbladresser t1
WHERE NOT EXISTS (SELECT * FROM tbladresser t2 WHERE t1.owner_id=t2.owner_id AND t2.atype_idx=1)


I dont understand what the problem is with NULL values! If a field is NULL already then the field must accept NULL values! Are you saying that it doesnt?

Actually the two SQL are very similar, only difference is how we find the required records.

Jeg har nu inkluderet de felter som ikke kan være "NULL" så det er løst.
Men til gængeld er der et felt "address_id" som skal have det samme nr. + 1 som er i den post som skaber tvillingen

Its because i havent included all the fields. But i have now :-)
Do you completly understand Danish or do you have a translater???
Terry..

I dont see a field named address_id!

But you would use


SELECT ..., [address_id]+1, ......

I have a transaltor :o)

No I've been in DKfor quite a few years now so I understand almost everything, I'm just Toooo lazy, and danes are good at english :o)

hehe...
ok

I´ve just forgot too give all the fields on the tabel.
There are 15 fields :-)
i´m a little lazy my self

Jeg tror at jeg er lidt handicappet - jeg har ikke været med i det foregående
spørgsmål

When i try too run this, it complains about a "incorrect syntax near ')' ???

INSERT INTO crm5.address
                      (address_id, owner_id, atype_idx, zipcode, city, county, updatedCount, updated_associate_id, updated, registered_associate_id,)
SELECT    address_id + 1 AS Expr2, owner_id, 1 AS Expr1, zipcode, city, county, updatedCount, updated_associate_id, updated, registered_associate_id,
                      registered
FROM        crm5.address
WHERE    (atype_idx = 2) AND (owner_id NOT IN
                          (SELECT    T.Owner_ID
                            FROM          crm5.address T
                            WHERE      atype_idx = 1))

INSERT INTO crm5.address
                      (....

should be
INSERT INTO crm5 (....

you also have a , just before the closing ) to the INSERT

make sure that there is the same number of fields in the SELECT as there are inthe INSERT and that they are in the correct order!

Sorry ... my faul
:-)

address is the tabel where the fields are in...
So why not INSERT INTO crm5.address ?

oh! I thought crm5 was the table name and address the field :o)

hehe ok...

Nu er der et nyt problem :-(

Cannot insert dublicate key row in object 'address' with unique index 'IDXAddrId'

Med det menes at der ikke kan være 2 poster med "address_id" men  det er det jeg prøver at undgå ved at tilføje +1
"SELECT    address_id - 1 AS Expr2,..."

ehhh tilføje -1..

Den er ellers sikker.
Hver gang der findes en post "address_id" = x, som ikke har en tvillinge post er tallet "address_id" = x+1 ikke brugt

Hvordan tildeler man nye tal til "address_id" i forlængelse af dem der er i forvejen?

Ok jeg laver et nyt spg. :-)

Soryy, I've been away from my PC for a while!

The error "Cannot insert dublicate key row in object 'address' with unique index 'IDXAddrId'" indicates that you aretrying to create a record where the index 'IDXAddrId' already exists.
One or more fields in the table are used to make the index and it is these which you are trying to duplicate.

status?

zeus999>Are we finished with this question?

and?