Updating a file with SQL

[Guest.Visitor]

Yes, you direct output from one select statement into an update statement. Any SQL book will show you how.

Update CustMast set ( Phone, Fax ) = (select Phone, Fax from CustTrans where CustTrans.CustKey = CustMast.CustKey) Where exists (select * from CustTrans where CustTrans.CustKey = CustMast.CustKey)

You need the "where exists" because otherwise, if no match is found, the system would try to put null values into Phone and Fax and in this case, they don't allow null values. Chris

[michaelsoucy@netzero.net]

Updating a file with SQL

Chris, Thanks for the tip! I'll try what you suggested. Thanks again, Michael Soucy

[Guest.Visitor]

Updating a file with SQL

I have tried your tip, chris and I got rid of the msg with null values, but now I get a msg that says; Result of SELECT more than one row. What can I do to get rid of that one? Here's my code: update jorgen/miloma set lmmfit = (select mlbrem from jorgen/mitloc where mitloc.mlcono = miloma.lmcono and mitloc.mlitno = miloma.lmitno and mitloc.mlbano = miloma.lmbano) where exists (select * from jorgen/mitloc where mitloc.mlcono = miloma.lmcono and mitloc.mlitno = miloma.lmitno and mitloc.mlbano = miloma.lmbano)

[pclapham@core-mark.com]

Updating a file with SQL

Using "SELECT DISTINCT" would be my first idea for reducing the SELECT to one row.

[Guest.Visitor]

Updating a file with SQL

This is a guess... try putting "fetch first 1 rows only" at the end of the select. That makes it more like a CHAIN where it grabs only one record. Chris

[Guest.Visitor]

Updating a file with SQL

Hi Chris and Paul, I tried your suggestion Paul with DISTINCT but that didn't work. I don't know how to code "fetch first 1 rows only", into my select statement. Could you clarify that for me, please? Jorgen

[Guest.Visitor]

Updating a file with SQL

Jörgen Andersson wrote: > I tried your suggestion Paul with DISTINCT but that didn't work. What does "didn't work" mean? Same result, error message, etc. Bill

[Guest.Visitor]

Updating a file with SQL

Do a search. You'll get lots of hits. Select * from myfile Where myField = 'something' Fetch first 1 rows only Chris

[Guest.Visitor]

Updating a file with SQL

Using DISTINCT didn't work because I got the same message, namely; Result of SELECT more than one row. A big Thanks to you Chris for your last tip. I have never seen that one before. I have now solved my problem. Thanks for all the help!! BR Jorgen

[J.Pluta]

Updating a file with SQL

I am concerned, though, because this simply masks the underlying problem, which is that your select isn't specific enough. This is the SQL that selects the value used to update the other file: select mlbrem from jorgen/mitloc where mitloc.mlcono = miloma.lmcono and mitloc.mlitno = miloma.lmitno and mitloc.mlbano = miloma.lmbano The problem is that this is returning more than one value for mlbrem, because more than one record has a matching "cono", "itno" and "bano" (company, item and batch perhaps?). You "fixed" the problem by fetching only the first row (record), but how do you know the first record contains the correct value? Why is that select returning more than one value? Which value is correct? Just getting the syntax right doesn't mean the statement is correct! Joe

[michaelsoucy@netzero.net]

Updating a file with SQL

Does anyone know of a way to update a file using SQL with data from a second file? I have some data in one file that I want to use to update another file, and it appears that all you can't do it using the UPDATE commend in SQL. If I can't do it in SQL then I'll write a RPG program instaed. Any insight would be greatly appreciated. Michael Soucy

[Guest.Visitor]

Updating a file with SQL

Hi Joe, No, you are right about that. Just getting the syntax right does not necessarily mean you get the right data. But in this particular case, it really doesn't matter which value I use. It's OK to just pick one as long as I have a matching record on company, item and batch. And that has also been my problem to get the select-statement specific enough. It hasn't been possible because there are no more fields that match. Best Regards, Jorgen