PL/SQL 多表update

原创
2014/04/08 20:12
阅读数 1K

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);

    }

  };


展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
5 收藏
0
分享
返回顶部
顶部