Any help with an update/join/count?

Hey everyone,
I'm trying to come up with an update statement for this
dataset... it's a simplified version of the data
that I'm working with, so it might not make
a lot of sense as to why it's structured as it is...
Given these tables:
create table t1 (theKey1 int,
theKey2 int,
needToPutTheDataHere varchar(1),
tooFewError int default 0,
tooManyError int default 0)
insert into t1 values(1,1,'',0,0)
insert into t1 values(2,2,'',0,0)
insert into t1 values(3,3,'',0,0)
create table t2 (theKey1 int,
theKey2 int,
theData varchar(1))
insert into t2 values(1,1,'A')
insert into t2 values(1,1,'B')
insert into t2 values(2,2,'C')
I need to update so that I get this:
t1:
thekey1 thekey2 needToPutTheDataHere too
FewError tooManyError
1 1 NULL 0 1
2 2 C 0 0
3 3 NULL 1 0
- link t1 and t2 on thekey1 and thekey2
- put t2.theData in t1.needToPutTheDataHere if there is exactly one
matching row in t2
- set t1.tooFewError or t1.tooManyError to 1 if there is less
than one or more than 1 matching row in t2.
I could also eliminate the tooFew/tooMany columns and just use a
count (0,1,...x) if that would increase performance for large datasets.
Any help on how to write this update would be much appreciated!I think that you might want to use a VIEW that will always be right
instead of updating constantly.
While I am glad to get any DDL, you have no keys and a VARCHAR(1)
column -- think about that for two seconds. Also, we do not have links
in SQL -- that is assembly language; we have references and joins.
Let's use a tally instead of assembly language style flags:
CREATE TABLE Foobar
(thekey1 INTEGER NOT NULL,
thekey2 INTEGER NOT NULL,
needtoputthedatahere CHAR(1) DEFAULT '' NOT NULL,
tally INTEGER DEFAULT 0 NOT NULL,
INSERT INTO Foobar VALUES (1, 1, '', 0, 0);
INSERT INTO Foobar VALUES (2, 2, '', 0, 0);
INSERT INTO Foobar VALUES (3, 3, '', 0, 0);
CREATE TABLE NewFoobar
(thekey1 INTEGER NOT NULL,
thekey2 INTEGER NOT NULL,
thedata CHAR(1) NOT NULL,
PRIMARY KEY (thekey1, thekey2, thedata)); -- needs a key
INSERT INTO NewFoobar VALUES (1, 1, 'a');
INSERT INTO NewFoobar VALUES (1, 1, 'b');
INSERT INTO NewFoobar VALUES (2, 2, 'c');
Try this:
UPDATE Foobar
SET needtoputthedatahere
= (SELECT F1.thedata
FROM NewFoobar AS F1
WHERE Foobar.thekey1 = F1.thekey1
AND Foobar.thekey2 = F1.thekey2
GROUP BY F1.thekey1, F1.thekey2
HAVING COUNT(*) = 1) -- empty scalar table expressions become
NULL
tally =(SELECT COUNT(*)
FROM NewFoobar AS F1
WHERE Foobar.thekey1 = F1.thekey1
AND Foobar.thekey2 = F1.thekey2) -- empty table
expressions count zero
WHERE EXISTS
(SELECT *
FROM NewFoobar
WHERE Foobar.thekey1 = NewFoobar.thekey1
AND Foobar.thekey2 = NewFoobar.thekey2);
You can drop the WHERE clause on the UPDATE if you wish.