. SQL . SQL Update

SQL Update

SQL Update Example One

Q: Who Was in the Film "Monty Python and the Holy Grail"? 
   
 Select Thespian_Last_Name, Thespian_First_Name
   From Thespian_Film_Table 
    where Film_Title = "Monty Python and the Holy Grail";

   
 Balin, Michael
 Chapman, Graham
 Cleese, John
 Gilliam, Terry
 Idle, Eric
 Jones, Terry
Q: You have Michael Palin listed as Michael Balin in your Thespian_Film_Table. Fix it! 
   
 Update Thespian_Film_Table
  Set Thespian_Last_Name  = "Palin" 
    where Thespian_First_Name = "Michael"
      and Thespian_Last_Name = "Balin";
Note: This would change every Thespian_Last_Name = "Balin" to "Palin" where Thespian_First_Name = "Michael". It won't just update for Film_Title = "Monty Python and the Holy Grail", it will update all Michael Balin films.
Also Note: This would change the Thespian_Last_Name to "Palin" where Thespian_First_Name is "Michael" and Thespian_Last_Name is "Balin". We do have to check the Thespian_First_Name to make certain we have the correct Balin, but we don't need to update Thespian_First_Name because it is correct when we start.
Q: Now please show it with the names corrected. 
   
 Select Thespian_Last_Name, Thespian_First_Name
   From Thespian_Film_Table 
    where Film_Title = "Monty Python and the Holy Grail";

   
 Chapman, Graham
 Cleese, John
 Gilliam, Terry
 Idle, Eric
 Jones, Terry
 Palin, Michael
Sign In
to add your own comment
Comment by TheMadProfessor Rate this Comment

The UPDATE Anonymous mentions should work if the following things are true:

1) You know where, what and how long the existing text in the field is that you want changed
2) The above is the same in every case
3) What you want to change it to is the same in every case and
4) For a given row being changed, your implementation of SQL is able to directly reference the pre-change data in the field.

If the part being replaced varies from row to row, you would most likely have no choice but to do it cursor-based, since you would have to analyze the current contents of the field to determine what to keep and what to replace.

Comment by Anonymous Rate this Comment

It might depend on your specific flavour of SQL, but the following worked for me with Derby/Cloudscape/JavaDB:
"UPDATE PHOTOS SET FILE_PATH = '$(rootdir)\144' + SUBSTR(FILE_PATH, 22)"

Another syntax you could try is:
"UPDATE PHOTOS SET FILE_PATH = '$(rootdir)\144' + RIGHT(FILE_PATH, LEN(FILE_PATH) - 21)"

Comment by TheMadProfessor Rate this Comment

No way to do this that I'm aware of with SQL alone. This sort of thing is more conducive to solving with an actual programming language such as PL/SQL, Transact-SQL, Visual Basic, etc. The typical methodology would be via a cursor (a term for a temporary table populated by the result set of a SELECT and traversed one row at a time):

Define cursor with selection criteria
Open cursor (this populates the rows)
Loop (until all rows fetched):
Fetch a row
Determine if row requires updating and, if so, update WHERE CURRENT OF cursor id
endloop
Close cursor

Comment by archive Rate this Comment

I am looking for some SQL help, I am tring to change information in a table to
a specific value here is an example of how the data looks.

\\condor\vault\amstar\flood\example.tif
\\condor\vault\amstar\flood\example2.tif
\\condor\vault\amstar\flood\example3.tif

I need to change this data to read

$(rootdir)\144\flood\example.tif
$(rootdir)\144\flood\example2.tif
$(rootdir)\144\flood\example3.tif

So far i canot find any commands that will only allow me to change just part of
the data "\\condor\vault\amstar"

Please let me know if you know any way to get this done.