update query not working

[chainedtodesk]

i have a table that an occasional user will not populate a major field, and i need to catch this before it goes any further. i tried the following code for an update prior to pushing this data out but the query fails. the two tables are large and have multiple records but the main links are distinct and should in theory fix this issue. i am sure its a small code error somewhere but i have rewritten it several times and ways and still it fails. any thoughts?? thanks (error is t1 not defined)

UPDATE RKBOOKTEST t0
SET t0.JQCHNB = t1.JQCHNB
from
(
select distinct JQCVNB,JQCANB,JQC0CD,JQCHNB
from amflib.MTHACTT0 where JQCHNB not in (0,1)
) t1
WHERE
t0.JQCHNB = 0 and
t0.JQCVNB = t1.JQCVNB and
t0.JQCANB = t1.JQCANB and
t0.JQC0CD = t1.JQC0CD

 

[imex]

Hi,

Try:

UPDATE t0 
SET t0.JQCHNB = t1.JQCHNB
from RKBOOKTEST t0
inner join
(
    select distinct JQCVNB,JQCANB,JQC0CD,JQCHNB 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)
) t1
    ON
        t0.JQCVNB = t1.JQCVNB and
        t0.JQCANB = t1.JQCANB and
        t0.JQC0CD = t1.JQC0CD
WHERE 
    t0.JQCHNB = 0

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[chainedtodesk]

thanks for that response, i tried that and recived the following error "Column qualifier or table T1 undefined." i also tried to just use JOIN instead of INNER but same error.

 

[SQLBill]

Since you are still testing this, I suggest making it a SELECT statement so you don't inadvertantly update records.

SELECT t0.JQCHNB
from RKBOOKTEST t0
inner join
(
    select distinct JQCVNB,JQCANB,JQC0CD,JQCHNB 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)
) t1
    ON
        t0.JQCVNB = t1.JQCVNB and
        t0.JQCANB = t1.JQCANB and
        t0.JQC0CD = t1.JQC0CD
WHERE 
   t0.JQCHNB = 0

One benefit is you will see how many records will be changed by the update statement.

Now try changing the subquery to add the AS:

(
    select distinct JQCVNB,JQCANB,JQC0CD,JQCHNB 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)
) AS t1

see if that makes a difference.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[chainedtodesk]

SQLBill, i run the select statement and it returns just the 12 records that are 0 (zero) at this time, which are the ones that need to be updated. i then added the "AS" portion to the select and again it returned the 12 recs. i then retried the update code with the AS included as well but still get that same error (Column qualifier or table T1 undefined). i also created a quick table to replace the subquery select portion but again got the same error. ugh

UPDATE t0
SET t0.JQCHNB = t1.JQCHNB
from RKBOOKTEST t0
join RKBOOKUPD t1
ON
t0.JQCVNB = t1.JQCVNB and
t0.JQCANB = t1.JQCANB and
t0.JQC0CD = t1.JQC0CD
WHERE
t0.JQCHNB = 0

 

[SQLBill]

Why are you selecting all these columns, when you are only using one column in the UPDATE?

UPDATE t0 
SET t0.[highlight #FCE94F]JQCHNB[/highlight] = t1.[highlight #FCE94F]JQCHNB[/highlight]
from RKBOOKTEST t0
inner join
(
    select distinct [highlight #FCE94F]JQCVNB,JQCANB,JQC0CD,JQCHNB[/highlight] 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)
) t1
    ON
        t0.JQCVNB = t1.JQCVNB and
        t0.JQCANB = t1.JQCANB and
        t0.JQC0CD = t1.JQC0CD
WHERE 
    t0.JQCHNB = 0 [highlight #FCE94F][/highlight]

Wouldn't this subquery do the same thing?

 select distinct [highlight #FCE94F]JQCHNB[/highlight] 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)

If so, try this (make a backup of the table first).

UPDATE t0 
SET t0.JQCHNB = (SELECT DISTINCT JQCHNB FROM from amflib.MTHACTT0 where JQCHNB not in (0,1))
from RKBOOKTEST t0
inner join
(
    select distinct JQCHNB 
    from amflib.MTHACTT0 where JQCHNB not in (0,1)
) t1
    ON
        t0.JQCVNB = t1.JQCVNB and
        t0.JQCANB = t1.JQCANB and
        t0.JQC0CD = t1.JQC0CD
WHERE 
    t0.JQCHNB = 0

I'm thinking that the t1 in the SET doesn't exist yet.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Bill, I think those columns need to be returned because they are used in the join condition.

Column qualifier or table T1 undefined

This looks like an error message you might get from DB2, not Microsoft SQL Server. If you are actually using DB2, you might want to seek advice in a DB2 specific forum. This one is for Microsoft SQL Server.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chainedtodesk]

SQLBill - the other 3 fields that i am drilling by are needed to make the exact match when updating the record with the JQCHNB = 0, they are the other specific identifiers to the main records which have several other data buckets that may not be similar

 

[gmmastros]

tek-tips has a DB2 forum here: forum178



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chainedtodesk]

problem solved, thanks for all that helped: here is the working code:

UPDATE RKBOOKTEST SET (JQCHNB) =
(SELECT RKBOOKUPD.JQCHNB
FROM RKBOOKUPD
WHERE RKBOOKTEST.JQCVNB = RKBOOKUPD.JQCVNB
AND RKBOOKTEST.JQCANB = RKBOOKUPD.JQCANB
AND RKBOOKTEST.JQC0CD = RKBOOKUPD.JQC0CD
AND RKBOOKUPD.JQCHNB not in (0,1)
AND RKBOOKTEST.JQCHNB = 0)
WHERE EXISTS
(SELECT RKBOOKUPD.JQCHNB
FROM RKBOOKUPD
WHERE RKBOOKTEST.JQCVNB = RKBOOKUPD.JQCVNB
AND RKBOOKTEST.JQCANB = RKBOOKUPD.JQCANB
AND RKBOOKTEST.JQC0CD = RKBOOKUPD.JQC0CD
AND RKBOOKUPD.JQCHNB not in (0,1)
AND RKBOOKTEST.JQCHNB = 0)