有一张表users
LOGINNAME SEX TYPE
NAME1 2 0
NAME2 1 1
NAME3 1 2
我想把NAME1的SEX改成NAME3的SEX,TPYE改成NAME2的TYPE
请问大家如何改 用SQL语句实现 一条可以吗?
本人新手 谢谢大家
LOGINNAME SEX TYPE
NAME1 2 0
NAME2 1 1
NAME3 1 2
我想把NAME1的SEX改成NAME3的SEX,TPYE改成NAME2的TYPE
请问大家如何改 用SQL语句实现 一条可以吗?
本人新手 谢谢大家
b as select type from user where loginname=name2
update user set sex,type = (select sex,type from a,b) where loginname = name1
create table users
as
select 'name1' loginname,2 sex,0 type from dual union all
select 'name2',1,1 from dual union all
select 'name3',1,2 from dual;
--NAME1的SEX——NAME3的SEX,TPYE改成NAME2的TYPE
update users
set sex=(
select sex
from users
where loginname='name3'
)
where loginname='name1';
--
update users
set type=(
select type
from users
where loginname='name2'
)
where loginname='name1';
--
SQL> select * from users;LOGINNAME SEX TYPE
--------- ---------- ----------
name1 1 1
name2 1 1
name3 1 2
--cte表可以搞定,没想到,...
with a as(
select sex
from user
where loginname='name3')
,b as (
select type
from user
where loginname='name2')
update user
set sex,type = (
select sex,type from a,b
)
where loginname = 'name1'
两位都是高手哈哈 我看明白意思了 不过格式我不懂。。
/*
在数据库中,with子句叫做common table expression
用途很多,一般用于测试用,你可以google一下
*/
SQL> with t as(
2 select * from emp
3 )
4 select * from t;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3100.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 1014 rows selectedSQL>
SQL> with t as(
2 select * from emp
3 )
4 select * from t
5 where deptno in(30);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-03 950.00 306 rows selected
可是我的问题。。他还是说没有找到select关键字。。
set sex = (select sex from users where loginname = 'NAME3'),
tpye = (select tpye from users where loginname = 'NAME2')
where loginname = 'NAME1'
--这么简单的问题都不会
update users set sex=1,TYPE=1 where LOGINNAME='NAME1'
create table users
as
select 'name1' loginname,11 sex,0 type from dual union all
select 'name2',22,1 from dual union all
select 'name3',33,2 from dual;
--
SQL> update users
2 set sex=(select sex from users where loginname='name3'),
3 type=(select type from users where loginname='name2')
4 where loginname='name1';1 row updatedSQL> select * from users
2 /LOGINNAME SEX TYPE
--------- ---------- ----------
name1 33 1
name2 22 1
name3 33 2
update users set SEX=(select SEX from users where LOGINNAME ='NAME3'),
type=(select TYPE from users where LOGINNAME ='NAME2')
where LOGINNAME ='NAME1'