sql server 的update语句_sql server update语句

注：修改表不能命名

1、如何在空列中复制表中的一列值;

UPDATE  dbo.test
SET     flag_updated = order_flag;

 2、修改test表中province_id字段。province为'北京',province_id=1，province为'安徽',province_id=224649;

UPDATE  dbo.test
SET     province_id = ( CASE WHEN province = '北京' THEN 1
                             WHEN province = '安徽' THEN 224649
                        END );

3、修改test表中member_register_id，修改条件为test1.phone = test.mobile，修改值test1.id;

UPDATE  dbo.test 
SET     member_register_id = ( SELECT  b.id 
                               FROM    dbo.test1 b
                               WHERE   b.phone = test.mobile );

4、修改test表中id值为1的order_flag字段值，修改值为'sq';

UPDATE  dbo.test 
SET     order_flag = 'sq' 
WHERE   id =1;

 5、修改test表中的order_flag，修改条件为test.order_no = test1.order_no;

UPDATE  dbo.test
SET     order_flag = 1
WHERE   order_no in ( SELECT  order_no 
                      FROM    dbo.test1 );

6、修改test表中id值为1的coumn1和coumn2字段值;

UPDATE  dbo.test
SET     coumn1 = '北京' ,
        coumn2 = '安徽' 
WHERE   id = 1 ;

7、修改test表中的member_register_id和username，修改条件为test1.mobile = test.phone，修改值为test1的id和name列。

UPDATE  dbo.test
SET     member_register_id = b.id ,
        username = b.name 
FROM    dbo.test1 b
WHERE   b.mobile = test.phone ;