Select into problem

[DaveJohnson]

Hi

Could anyone tell me where i am going wrong the first part works fine and updates existing records but the second half does not insert any new records.


UPDATE SY01200 SET

INET1 = (SELECT EMAIL FROM F100_VM_EMAIL WHERE SY01200.MASTER_ID = F100_VM_EMAIL.ACCOUNT),
INET8 = (SELECT NOTES FROM F100_VM_EMAIL WHERE SY01200.MASTER_ID = F100_VM_EMAIL.ACCOUNT)

FROM SY01200, F100_VM_EMAIL
WHERE SY01200.MASTER_ID IN (SELECT ACCOUNT FROM F100_VM_EMAIL) AND MASTER_TYPE = 'VEN'


INSERT INTO SY01200 (Master_Type, Master_ID, ADRSCODE, INETINFO, INET1, INET2, INET3, INET4, INET5, INET6, INET7, INET8)

SELECT 'VEN', F100_VM_Email.ACCOUNT, F100_VM_Email.ACCOUNT, ' ', F100_VM_Email.EMAIL, ' ', ' ', ' ', ' ', ' ', ' ', F100_VM_Email.NOTES

FROM SY01200, F100_VM_EMAIL
WHERE F100_VM_EMAIL.ACCOUNT NOT IN (SELECT MASTER_ID FROM SY01200)

Many Thanks

 

[bborissov]

And the error is?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DaveJohnson]

There is no error reported just affects 0 records when I know there are records in F100_VM_EMAIL and not in SY01200.

Thanks

 

[gmmastros]

Make a backup of your database before you run this (just in case).

INSERT 
INTO   SY01200(Master_Type, Master_ID, ADRSCODE, INETINFO, INET1, INET2, INET3, INET4, INET5, INET6, INET7, INET8)
SELECT 'VEN', F100_VM_Email.ACCOUNT, F100_VM_Email.ACCOUNT, ' ', F100_VM_Email.EMAIL, ' ', ' ', ' ', ' ', ' ', ' ', F100_VM_Email.NOTES
FROM   F100_VM_EMAIL
       Left Join SY01200
         On F100_VM_EMAIL.ACCOUNT = SY01200.MASTER_ID
Where  SY01200.MASTER_ID Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

I just read carefully your query. I have some notes:

First you don't need SY01200 table at all (in your SELECT part of the query).
Second, if you need it you didn't put ANY join statement between sys01200 and F100_VM_Email in your query.


George query must works for you.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DaveJohnson]

Backup and inserted your code and it worked fine.

Thank you

George