PL/SQL 多表update

T-SQL和PL/SQL表关联更新语句的区别

SQLserver与Oralce中的两表关联更新是区别的。

在T-SQL中更新相对简单些，直接用下面的语句都是可以：

Update b
Set b.processflag = a.processflag
From temp_1 a Join temp_2 b
On a.msgid_db = b.msgid

Update temp_2
Set Usertime = b.Usertime
From temp_2 a Join temp_1 b
On a.msgid = b.msgid_db

Update temp_2
Set Usertime = b.Usertime
From temp_1 b
where msgid = b.msgid_db

而在Oracle中这样写是不对的，应该更改如下：

Update Temp_2 a
   Set a.Usertime = (Select b.Usertime
             From Temp_1 b
             Where b.Msgid_Db = a.Msgid)
Where Exists (Select 1 From Temp_1 b Where b.Msgid_Db = a.Msgid);

更新多个字段：

Update Temp_2 a
   Set a.Usertime = (Select b.Usertime
             From Temp_1 b
             Where b.Msgid_Db = a.Msgid),
       a.processflag = (Select b.processflag
             From Temp_1 b
             Where b.Msgid_Db = a.Msgid)
Where Exists (Select 1 From Temp_1 b Where b.Msgid_Db = a.Msgid);

多表关系更新：

Update Temp_2 a
   Set a.Usertime = (Select b.Usertime
             From Temp_1 b
             Where b.Msgid_Db = a.Msgid),
       a.processflag = (Select c.processflag
             From Temp_3 c
             Where c.Msgid = a.Msgid)
Where Exists (Select 1 From Temp_1 b Where b.Msgid_Db = a.Msgid)
        And Exists (Select 1 From Temp_3 c Where c.Msgid = a.Msgid);

感觉这点没sqlserver理简单易用~~~

我在项目里面是这样写的 

public void updateAddress(String addressId, String zipCode) throws Exception {

    DBean db = new DBean();

    PreparedStatement ps = null;

    ResultSet rs = null;

    ResultSet rs1 = null;

    StringBuffer buf1 = new StringBuffer();

    StringBuffer buf = new StringBuffer();

    buf1

        .append("select t.city ,t.district, t.street_type,t.street_name,t.street_zipcode,t.city_code,t.state from t_cts_int_zipcode_data t");

    buf1.append("  where t.street_zipcode=? ");

    buf

        .append("update t_pty_address t1 set t1.address01=? ,t1.address02=? ,t1.address03=? ,");

    buf.append(" t1.address04=? ,t1.address07=?,t1.address08=?, ");

    buf.append(" t1.region_lv2=? ");

    buf.append("  where t1.address_id=? ");

    Log.info(this.getClass(), buf.toString());

    try {

      Log.info(this.getClass(), "update begin: " + getClass().getName());

      db.connect();

      Connection con = db.getConnection();

      ps = con.prepareStatement(buf1.toString());

      ps.setString(1, zipCode);

      rs = ps.executeQuery();

      while (rs.next()) {

        ps.clearParameters();

        ps = con.prepareStatement(buf.toString());

        int i = 1;

        ps.setString(i++, rs.getString("CITY"));

        ps.setString(i++, rs.getString("DISTRICT"));

        ps.setString(i++, rs.getString("STREET_TYPE"));

        ps.setString(i++, rs.getString("STREET_NAME"));

        ps.setString(i++, rs.getString("STREET_ZIPCODE"));

        ps.setString(i++, rs.getString("CITY_CODE"));

        ps.setString(i++, rs.getString("STATE"));

        ps.setString(i++, addressId);

        ps.executeUpdate();

      }

      Log.info(this.getClass(), "update end: " + getClass().getName());

    } catch (SQLException e) {

      throw ExceptionFactory.parse(e);

    } catch (ClassNotFoundException e) {

      throw ExceptionFactory.parse(e);

    } finally {

      DBean.closeAll(rs, ps, db);

    }

  };

转载于:https://my.oschina.net/ruibo/blog/220102