AS400 SQL "NEWBIE"

[purefreshair]

Hello,

Am interested in using SQL to update a file on our AS400. The file has a description field (I'll call "desc1" that needs to be updated. I want to use a description field from a second file (I'll call "desc2") that has a description field that is up to date. Both files have other key fields from which I can join or use the where clause to perform this task. My Question(s) are:
Can use Interactive SQL, i.e. STRSQL do this?

I suspect the code would look something like the following:

Update mylib/file1
set desc1 =
(select desc2 from mylib/file2
where file1.key1 = file2.key2 and
file1.site1 = file2.site2)

.....uh, I sort of am stuck from this point. In fact, I do not know if this is the best approach.

Again, I'm a "newbie" to SQL but any help would be greatly appreciated.

Thanks.

 

[purefreshair]

Oops, forget my first post. I think I have the code figured out, but the column/field that I need to update can exist as a null value. My code looks like this:

update knrlib/itmrvme
set (uu40) =
(Select uu40 from knrlib/itmrvyou
where itmrvme.itnbr = itmrvyou.itnbr and
itmrvme.stid = '2')
where itmrvme.itnbr in ( select itmrvyou.itnbr
from knrlib/itmrvyou)

I got this example from a power tips book since I'm using a table "lookup" in an update statement. If I run this however I get "null values not allowed in column or variable UU40". There are conditions where this field can be null/blank. Anyway to get around this?

Thanks

 

[flapeyre]

Try

update knrlib/itmrvme
   set (uu40) =
       (Select uu40 from knrlib/itmrvyou
         where itmrvme.itnbr = itmrvyou.itnbr and
               itmrvme.stid = '2'
               [b]and itmrvyou.uu40 is not null)[/b]
         where itmrvme.itnbr in ( select itmrvyou.itnbr 
            from knrlib/itmrvyou)

I'm assuming itmrvyou.uu40 is null-capable in the file (as opposed to just having blanks). If it isn't, then replace the is not null with <> ' ', or add the condition to exclude blanks.

De mortuis nihil nisi bonum.

 

[purefreshair]

Hello Flapeyre,

Thanks for your post. If I add either version of what you suggest however, it comes back with "Null values not allowed in column or variable UU40".

Every record in my itmrvyou file has a value populated in UU40. The primary file, itmrvme (one I'm trying to update ) does have conditions where the UU40 field is blank.

 

[flapeyre]

Oh, Okay.

If I'm understanding this correctly, you don't want to update the target field itmrvme.uu40 if it's blank? If so:

update knrlib/itmrvme
   set (uu40) =
       (Select uu40 from knrlib/itmrvyou
         where itmrvme.itnbr = itmrvyou.itnbr and
               itmrvme.stid = '2'
          where itmrvme.itnbr in ( select itmrvyou.itnbr 
            from knrlib/itmrvyou)
            [b]and itmrvme.uu40 <> ' '[/b]

If you do want to update itmrvme.uu40 only if it's blank, change the '<>' to an equals sign.

There is a difference in DB2 between a "null" value and a "blank" value. A null-capable field (column) can have a null indicator set (but the data is still there). A blank field has no data in it at all, and may be null-capable, but with the null indicator set off, or (more likely) a non-null capable field (like yours) with nothing in it.

De mortuis nihil nisi bonum.

 

[purefreshair]

Flapeyre,

I still get the "Null values...." message either way.

I want to update the target field itmrvme.uu40 with the value found in itmrvyou.uu40 regardless of whether the target field is blank or has data in it. Either case can exist in the target field. I'm using the itnbr field in both tables to match the rows in order to perform the update.

 

[flapeyre]

update knrlib/itmrvme
   set (uu40) =
       (Select uu40 from knrlib/itmrvyou
         where itmrvme.itnbr = itmrvyou.itnbr and
               itmrvme.stid = '2')
[code]

Sorry, it's late, and I just saw some redundancies - try the above. 

[i][b]De mortuis nihil nisi bonum.[/b][/i]

 

[purefreshair]

Same Results.

FYI, I am mearly doing a "STRSQL" from a command line and I'm attempting to run this interactively. Could that have anything to do with why it doesn't work?