小弟求一个关于更新的sql语句

[darkroom]

我有一个表:
Table A
--------------------------------
Tag Count
--------------------------------
a 0
b 0
----------------------------
还有一个表：

Table B
--------------------------------
Name Tag
-------------------------------
1 a
2 b
3 a
4 c
----------------------------------
我想更新表A的count字段，使值为在表B里出现的次数，如果该tag未出现，则新增一条数据，对于上面的运行结果应该是
Table A
------------------------------------
Tag Count
--------------------------------
a 2
b 1
c 1
---------------------------------------------------------------
想问一下各位高手这个语句怎么写，小弟感激不尽。

[天下如山]

个人感觉一条语句难 呵呵 期待下面高手.

[王向飞]

分两步 
第一步 delete from A
第二部

insert  into a (tag,count)
select tag,count(1) from b group by tag

[dawugui]

--需要两条语句.

update a set [count] = (select count(1) from b where b.tag = a.tag)
insert into a select tag , count(1) from b where tag not in (select tag from a) group by tag

[dawugui]

create table a(Tag varchar(10), [Count] int)
insert into a values('a', 0)
insert into a values('b', 0)
create table B(Name int,Tag varchar(10))
insert into b values(1 ,'a')
insert into b values(2 ,'b')
insert into b values(3 ,'a')
insert into b values(4 ,'c')
go

update a set [count] = (select count(1) from b where b.tag = a.tag)
insert into a select tag , count(1) from b where tag not in (select tag from a) group by tag

select * from a

drop table a , b

/*
Tag        Count       
---------- ----------- 
a          2
b          1
c          1

（所影响的行数为 3 行）
*/

[fpzgm]

--方法一
update A set [count] = (select count(*) from B where tag = A.tag)
insert into A 
select tag , count(*) from B where tag not in (select tag from A) group by tag

--方法二
delete from A where tag in (select tag from B)
insert into A 
select tag , count(*) from B group by tag

[fpzgm]

create table A(Tag varchar(10), [Count] int)
insert into A values('a', 0)
insert into A values('b', 0)
create table B(Name int,Tag varchar(10))
insert into B values(1 ,'a')
insert into B values(2 ,'b')
insert into B values(3 ,'a')
insert into B values(4 ,'c')
go
--方法一
update A set [count] = (select count(*) from B where tag = A.tag)
insert into A 
select tag , count(*) from B where tag not in (select tag from A) group by tag

--方法二
delete from A where tag in (select tag from B)
insert into A 
select tag , count(*) from B group by tag

/*
Tag        Count       
---------- ----------- 
a          2
b          1
c          1

（所影响的行数为 3 行）
*/

[darkroom]

问题已解决，谢谢大家

[songguozhi]

IF OBJECT_ID('A') IS NOT NULL 
	DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL 
	DROP TABLE B
GO
CREATE TABLE A(Tag VARCHAR(32), count INT)
CREATE TABLE B(NAME VARCHAR(32), Tag VARCHAR(32));

INSERT INTO A
SELECT 'a', 0 UNION ALL
SELECT 'b', 0

INSERT INTO B
SELECT '1', 'a' UNION ALL
SELECT '2', 'b' UNION ALL
SELECT '3', 'a' UNION ALL
SELECT '4', 'c' 
go
IF OBJECT_ID('sp_UpdateOrInsertTagCount') IS NOT NULL 
	DROP PROCEDURE sp_UpdateOrInsertTagCount
go
CREATE PROCEDURE sp_UpdateOrInsertTagCount
AS
BEGIN
	UPDATE A SET [COUNT] = (SELECT COUNT(1) FROM B WHERE B.Tag = A.Tag)
	INSERT INTO A(Tag, [count]) 
		SELECT tag , count(1) 
		FROM B 
		WHERE Tag not in (SELECT Tag FROM A) 
		GROUP BY Tag
	
	SELECT * FROM A
END
go
EXEC sp_UpdateOrInsertTagCount