Update table with record from another table

[jeninco]

I've seen suggestions for variations of this request, but when I tried them, it ignored my data selection and indicated it was going to update the entire file, which is NOT ok. (I only want to update the records that don't already match.) What I tried is:
update f4106
set bplitm = select (imlitm from f4101 where f4101.imitm = f4106.pbitm)
where f4106.bplitm <> f4101.imlitm
I get the error: &quot;Column qualifier or table F4101 undefined.&quot;

What am I doing wrong?

[sig][/sig]

 

[listener22]

try putting the paranthesis over one word to the left in the second line:

update f4106
set bplitm = (select etc. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[jeninco]

Oops! That was my typo. I do have the parenthsis there. What's puzzling, is if I set my last where statement to:
Where f4106.bpitm = 1247
it works. But I don't want to have to do this update one record at a time!!!
[sig][/sig]

 

[listener22]

Ah.
You can not directly use another table inside the where clause.
You're gonna have to use either 'not exists' or 'not in', such as
all your stuff above except the last line plus


where f4106.bplitm not in (select f4101.imlitm from f4101)

(and hope that your f4101 table is not very big)
or

where not exists
(select 'x' from f4101, f4106
where f4101.imlitm = f4106.pbitm)


(and hope that your tables are small, or appropriately indexed)

(these kinds of updates can take a long time with large tables)

(It's all because the update statement only really 'knows' about
the table being updated.) [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[jeninco]

Well, it acted like it was going to work, but then I got a &quot;Null values are not allowed in column bplitm in table F4106.&quot; It went on to say: &quot; If this is an UPDATE statement then the null value was specified in the relative entry 1 in the SET clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL.
I checked all of my F4101/imlitm records that I am updating from and none of them are blank. Any ideas??? ~jen [sig][/sig]

 

[bigscouse]

Would this work?

update f4106 A
from f4101 B
set bplitm = B.imlitm
where exists (
select *
from f4101 C
where C.imitm = A.pbitm )
and B.imlitm <> A.bplitm;

Note if your tables are big then &quot;where exists&quot; is the best option as it will only scan the table till the condition is met however a normal &quot;where&quot; clause will scan the full table [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[jeninco]

I tried it and I got a syntax error: &quot; Keyword FROM not expected. Valid tokens: SET.&quot; I took out the &quot;FROM&quot; statement and replaced it with &quot;Set bplitm=f4101.imlitm&quot; and got the original error &quot;Column qualifier or table F4101 undefined.&quot; Still looking for suggestions!!! ~jen [sig][/sig]

 

[jeninco]

Can someone to help me with the &quot;order of operations&quot; with this statment. I finally got an update to work, kinda. The update ran, and said &quot;Row not found for update.&quot; I need help knowing which statements are being looked at first, so I can figure out which row it can't find and why. I am determined to figure this out.... ~jen
update f4106 set bplitm = (select imlitm from f4101 where f4101.imitm = f4106.bpitm) where not exists (select * from f4101, f4106 where f4101.imlitm = f4106.bplitm) [sig][/sig]

 

[carp]

Jen -
I set up abbreviated tables (just itm and litm columns) and got the following to work:

1. UPDATE f104 a
2. SET litm = (SELECT litm FROM f101 b
3. WHERE a.itm = b.itm)
4. WHERE (a.itm, a.litm) NOT IN
5. (SELECT itm,litm FROM f101);

The WHERE clause that begins at Line 4 will determine which rows in f104 need to be updated by performing a pair-wise comparison of the itm and litm values. The correlated subquery delivers the correct value for litm.
[sig][/sig]

 

[jeninco]

The WHERE statement in line 4 gave me a syntax error. &quot;Token , was not valid. Valid tokens: < + ) - > = <> <= ¬< ¬> ¬= >= IN&quot;
I removed one of the qualifiers in line 4 and 5.

4. WHERE (f4106.bplitm) not in
5. (SELECT imlitm from f4101)

The update ran and said &quot;Null values are not allowed in column BPLITM in table F4106&quot; It went on to explain, &quot;If this is an UPDATE statement then the null value was specified in the relative entry 1 in the SET clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL.&quot;
I triple-checked and none of the records I want to update from are Null. Any more ideas???? I'm willing to start from scratch with a new update/thought process....

here's the select statment:
select count (*) from f4106 where bpitm in
(select imitm from f4101 where imlitm <> bplitm)
All I want to do is update the 705 bplitm records in F4106 that <> the imlitm records in F4101 where bpitm = imitm
Still determined (and a little baffled)!!! ~jen [sig][/sig]

 

[calahans]

For your code:

update f4106
set bplitm = select (imlitm from f4101 where f4101.imitm = f4106.pbitm)
where f4106.bplitm <> f4101.imlitm

Should read: (For SQL Server)

UPDATE F4106
SET F4106.BPLITM= f4101.IMLITM
FROM F4101
WHERE F4106.PBITM=f4101.imitm
AND f4106.bplitm <> f4101.imlitm

For INGRES

Place the FROM cluse above the SET clause

[sig]<p>Cal<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[jeninco]

I tried:
update f4106 set f4106.bplitm=f4101.imlitm from f4101
where f4106.bpitm = F4101.imitm
and f4106.bplitm <> F4101.imlitm
and got: Token . was not valid. Valid tokens: =.
Then I tried:
update f4106 set bplitm=imlitm from f4101
where f4106.bpitm = F4101.imitm
and f4106.bplitm <> F4101.imlitm
and got: Keyword FROM not expected. Valid tokens: <END-OF-STATEMENT>.
oh...yea, by the way, I'm using this on AS/400..... [sig][/sig]

 

[carp]

OK, I think this SQL is vanilla enough for the job:

UPDATE f104 a
SET litem =
(SELECT b.litem
FROM f101 b
WHERE b.item = a.item)
WHERE EXISTS (SELECT litem
FROM f101 c
WHERE c.item = a.item
AND c.litem <> a.litem);

The WHERE EXISTS clause will prevent updating rows where f104 already has the right values. Without it, all rows will get updated. This wouldn't be so bad (outside of performance), but if a row in f104 already has the same value as f101 you run into an interesting problem. In that case, a NULL gets returned from the subquery - and that is what litem in f104 gets set to. Definitely suboptimal!

This code will work as long as there is no more than one row in f101 for each item value in the f104 table. If there are two or more, the update statement should blow up. [sig][/sig]

 

[jeninco]

Ahhhhh!!!! I only have one record in the F4101 file (your f101) but in some instances may have multiple records in the F4106 file (your f104). Because of that relationship does this mean I can't do this update or we just have to be more creative?? (I tried the update anyway, and it ran for about 8 minutes before I got the Null error.) ~jen
[sig][/sig]

 

[carp]

No, you should be fine. As long as you have a many-to-one relationship between f4106 and f4101 you will be fine. The problem would be if you had the one-to-many relationship. In that case, more than one value would be returned from f4101 and it would then blow up. [sig][/sig]

 

[Guest_imported]

update f4106
set bplitm = (select top 1 imlitm
from f4101
where f4101.imitm = f4106.pbitm)
where exists (select top 1 imlitm
from f4101
where f4101.imitm = f4106.pbitm);

I assume the &quot;imitm&quot; and &quot;imlitm&quot; fields are really different and not just typos. Who the hell named your tables/fields?

The &quot;Top 1&quot; syntax may be wrong for your SQL it may be replaced by &quot;Top 1 rows&quot; placed at the end of the selects.

The basis is that whatever select you use in an update (or insert) set statement is qualified in the where clause with an exists to ensure that no null values are inserted.

The &quot;Top 1&quot; element ensures that only one value can ever be inserted if your select statement ever returned more than one row (this is to be safe - you should really ensure that the select clause has sufficient where clauses to guarantee uniqueness or use an order by clause to ensure the top 1 will be the correct value where more than one is expected).

 

[jeninco]

To any who are interested.... I finally got a statment to work, but I had to update the entire file. A consultant believes we have a defect in SQL/400 because both of these updates should have worked, but only one did.
FAILED:
update f4106 BP1
set bplitm =
(select IM1.imlitm from f4101 IM1
where BP1.bpimt = IM1.imitm)
where BP1.bpitm in
(select IM2.imitm from f4101 IM2)
WORKED:
update f4106 BP1
set bplitm =
(select IM1.imlitm from f4101 IM1
where BP1.bpitm - IM1.imitm)
[sig][/sig]