mysql - sql insert into, select, where statement -


i want copy row hk_room table hk_history except row rstatus = '-' or rstatus='long stay' , rstatus = 'check out'.

'-' default value rstatus attribute.

i have tried these 2 query:

insert hk_history (rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate) select rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate hk_room1; rstatus <> '-'; 

or

insert hk_history (rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate) select rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate hk_room1; rstatus = 'long stay' , rstatus = 'check out'; 

but got error

you have semicolon before where in first query:

insert hk_history     (rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate)     select rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate     hk_room1;     -------------^     rstatus <> '-'; 

you need remove it.

to achieve this:

want copy row hk_room table hk_history except row rstatus = '-' or rstatus='long stay' , rstatus = 'check out'.

just use single statement:

insert hk_history     (rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate)     select rno,rstatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkr,fo_r,svr,rdate     hk_room1     not (rstatus = '-' or                 rstatus = 'long stay' , rstatus = 'check out'               ); 

you can phrase as:

    rstatus <> '-' ,           (rstatus <> 'long stay' or rstatus <> 'check out') 

however, previous version seems clearer intent.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -