What is Wrong with the Following Query
-
hi all i have the Following Query
if Exists(select * from sde.Property_Backup p1 where substring(p1.func_key,1,5)='GEOSS' aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key, p2.Attrib_code from sde.Property_Backup p2 where p2.func_key = 'PV000000' And p1.lis_key = p2.lis_key And P2.aCTIVE =1)) begin Update sde.Property_Backup p3 set p3.Attrib_code = p12.Attrib_code where p2.lis_key = p1.lis_key and substring(p2.func_key,1,5)='GEOSS' And P1.aCTIVE =1 endAnd i get the Following Error when run it,Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'p3'.what is wrong Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
-
hi all i have the Following Query
if Exists(select * from sde.Property_Backup p1 where substring(p1.func_key,1,5)='GEOSS' aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key, p2.Attrib_code from sde.Property_Backup p2 where p2.func_key = 'PV000000' And p1.lis_key = p2.lis_key And P2.aCTIVE =1)) begin Update sde.Property_Backup p3 set p3.Attrib_code = p12.Attrib_code where p2.lis_key = p1.lis_key and substring(p2.func_key,1,5)='GEOSS' And P1.aCTIVE =1 endAnd i get the Following Error when run it,Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'p3'.what is wrong Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
Vuyiswa wrote:
Update sde.Property_Backup p3 set p3.Attrib_code = p12.Attrib_code
Why are you including
sde.Property_Backup? Ifp3is a table, it would be right to write:Update p3 set p3.Attrib_code = p12.Attrib_code. -
Vuyiswa wrote:
Update sde.Property_Backup p3 set p3.Attrib_code = p12.Attrib_code
Why are you including
sde.Property_Backup? Ifp3is a table, it would be right to write:Update p3 set p3.Attrib_code = p12.Attrib_code.hi Thanks for your reply i wrote it like this
if Exists(select * from sde.Property_Backup p1 where substring(p1.func_key,1,5)='GEOSS' aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key, p2.Attrib_code from sde.Property_Backup p2 where p2.func_key = 'PV000000' And p1.lis_key = p2.lis_key And P2.aCTIVE =1)) begin Update p2 set p2.Attrib_code = p1.Attrib_code where p2.lis_key = p1.lis_key and substring(p2.func_key,1,5)='GEOSS' And P1.aCTIVE =1 end and It gives me an ErrorMsg 208, Level 16, State 1, Line 10
Invalid object name 'p2'.
/pre>.the Problem happenes on the Update. Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
-
hi Thanks for your reply i wrote it like this
if Exists(select * from sde.Property_Backup p1 where substring(p1.func_key,1,5)='GEOSS' aND Exists (select p2.Property_ID,p2.Lis_key,p2.Func_key, p2.Attrib_code from sde.Property_Backup p2 where p2.func_key = 'PV000000' And p1.lis_key = p2.lis_key And P2.aCTIVE =1)) begin Update p2 set p2.Attrib_code = p1.Attrib_code where p2.lis_key = p1.lis_key and substring(p2.func_key,1,5)='GEOSS' And P1.aCTIVE =1 end and It gives me an ErrorMsg 208, Level 16, State 1, Line 10
Invalid object name 'p2'.
/pre>.the Problem happenes on the Update. Thanks
Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za
How is your table called?
p2? Ifp2is the name of your table try the following update statement:UPDATE p2 SET p2.Attrib_code = p1.Attrib_code INNER JOIN p1 ON p1.lis_key = p2.lis_key WHERE Substring(p2.func_key,1,5) = 'GEOSS' AND P1.active = 1 -
How is your table called?
p2? Ifp2is the name of your table try the following update statement:UPDATE p2 SET p2.Attrib_code = p1.Attrib_code INNER JOIN p1 ON p1.lis_key = p2.lis_key WHERE Substring(p2.func_key,1,5) = 'GEOSS' AND P1.active = 1Thanks i have Used a Cursor to achieve this
DECLARE @CUR_LIS_KEY VARCHAR(20), @CUR_ATTRIB_CODE VARCHAR(12) DECLARE PARENT_CURSOR CURSOR FOR SELECT LIS_KEY, ATTRIB_CODE FROM SDE.PROPERTY_Summary P1 WHERE EXISTS(SELECT 1 FROM SDE.PROPERTY_Summary P2 WHERE P2.FUNC_KEY LIKE 'GEOSS%' AND P2.LIS_KEY = P1.LIS_KEY And p2.Attrib_code is Null And p2.Archive_Date is Null ) OPEN PARENT_CURSOR FETCH NEXT FROM PARENT_CURSOR INTO @CUR_LIS_KEY, @CUR_ATTRIB_CODE WHILE @@FETCH_STATUS = 0 BEGIN UPDATE SDE.PROPERTY_Summary SET ATTRIB_CODE = @CUR_ATTRIB_CODE WHERE FUNC_KEY LIKE 'GEOSS%' AND LIS_KEY = @CUR_LIS_KEY FETCH NEXT FROM PARENT_CURSOR INTO @CUR_LIS_KEY, @CUR_ATTRIB_CODE END DEALLOCATE PARENT_CURSORThanks for your HelpVuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za