0

there is a table user_info.

 CREATE TABLE USER_INFO 
   (USER_ID VARCHAR2(50 BYTE) PRIMARY KEY, 
    USER_NAME VARCHAR2(50 BYTE),
    USER_ROLE_TYPE VARCHAR2(50 BYTE)
   )

to extend the api and the admin(whose user_role_type is admin) , a new table reference to the table is created.

 CREATE TABLE ADMIN_INFO 
   (USER_ID VARCHAR2(50 BYTE), 
    USER_PHONE VARCHAR2(50 BYTE),
    USER_EMAIL VARCHAR2(100 BYTE)
   )

and the new table is refer to USER_INFO with foreign key.

ALTER TABLE ADMIN_INFO 
ADD CONSTRAINT FK_ADMIN_INFO
FOREIGN KEY (USER_ID) REFERENCES USER_INFO(USER_ID) ON DELETE CASCADE;

and the USER_INFO has already inserted some data to USER_INFO,and it do exists a row where user_id = '123'.

now I want only change the USER_PHONE, so i wrote the sql:

update ADMIN_INFO set
        USER_PHONE = '17096022212'
        where USER_ID = '123'

but when i try to select the row from the new table where user_id = '123',still nothing.

why?

and I do want save the new info to it ,and it is a edit-api which will show and can save the data in the page,how I make it?

if i want to give it a if-else judge,for the two situation and to sql differently.for example

if( the row exists){ update it }else{insert }

can i sql this in the mapper.xml?and if i can, how i wrote the sql?

-------------------3/10/2016/----------------------

now i finished a sql that has test ok

        merge into ADMIN_INFO d
        using(select '123' as USER_ID,
              '1234567' as USER_PHONE from dual) dd
        on(d.USER_ID = dd.USER_ID)
        when matched then
            update set d.USER_PHONE = dd.USER_PHONE
        when not matched then
            insert (USER_ID,USER_PHONE)
        values(dd.USER_ID,dd.DRIVER_NUMBER)

but,when i try to test like this:

        merge into ADMIN_INFO d
        using(select '1234' as USER_ID,
              __null__ as USER_PHONE from dual) dd
        on(d.USER_ID = dd.USER_ID)
        when matched then
            update set d.USER_PHONE = dd.USER_PHONE
        when not matched then
            insert (USER_ID,USER_PHONE)
        values(dd.USER_ID,dd.DRIVER_NUMBER)

the database shows a error,deadlocked,and the sql was keeping running and cannot finish.what happend?

daxue
  • 259
  • 1
  • 2
  • 11
  • From my experience, in the long term the most convenient and reliable database always appends data, rarely, or in few places, updates it, and never deletes it (except by archiving really old data). Storage is cheap, developers' sanity and data integrity / traceability are much more valuable. – 9000 Mar 09 '16 at 03:34

2 Answers2

1

UPDATE is used to alter an already existing row in that particular table - therefore if there is no row with USER_ID in the ADMIN_INFO table (the one you are trying to update), you need to INSERT it first

Marek Dusek
  • 75
  • 3
  • 8
0

What's the result of your update sql? Seems to me there is no such record in admin_info table. You should use insert instead of update if you want to insert a new record to a table.

Bigyellowbee
  • 123
  • 1
  • 12
  • as my thought firstly that the USER_ID has exists because it has refer to table USER_INFO,but now i got that i am wrong. – daxue Mar 09 '16 at 03:42
  • if i want to give it a if-else judge,for the two situation and to sql differently.for example if( the row exists){update it}else{insert},can i sql this in the mapper.xml?and if i can, how i wrote the sql? – daxue Mar 09 '16 at 03:47
  • @JOLee Here is a good discussion on this topic. [link] (http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – Bigyellowbee Mar 09 '16 at 13:44