Problem with Update query

Hi - I have 2 tables, which I would like to 'merge'.
tblAccess and tblCars
tblAccess:
a_id int (identity)
a_user (varchar50)
a_pass (varchar20)
a_cid (bigint)
tblCars:
c_id (identity)
c_user (varchar50)
c_carname (varchar50)
These were joined using the a_cid to c_id field, but now I want to copy
the tblAccess.a_user into the relative record in the tblCars.c_user
table.
Something like:
Update tblCars set tblCars.c_user = (select tblAccess.a_user from
tblAccess WHERE tblAccess.a_id = tblCars.c_id)
But I get an error advising the subquery returns more than 1 result.
Could anyone please help?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***>> But I get an error advising the subquery returns more than 1 result.
The error message suggests that you have more than one value for a_id in the
tblAccess table for each value of c_id in the tblCars table.
If you want to get the right value, you might have to look carefully into
the logic you use in the WHERE clause in your subquery. If all you want to
do is to avoid the error, then you can use an extrema aggregate like:
UPDATE tblCars
SET c_user = ( SELECT MAX( tblAccess.a_user )
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
WHERE EXISTS ( SELECT *
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
The WHERE clause is to constrain the statement to update only the values
that have matching values for the identifier columns in both the tables.
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I guess that you meant to post something like this, assume that you
know that IDENTITY is not ever a key , how to do proper data element
names:and followed industry standards. And not put "rbl-" suffixes on
table names or table anem suffixes on column names.
This is still bad, but it is not awful:
CREATE TABLE VehicleAccess
(vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
vehicle_pass VARCHAR (50) NOT NULL,
vin CHAR(17) NOT NULL --industry std
REFERENCES Cars(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (vehicle_user, vehicle_pass) -- wild guess by me
);
CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY, --industry std
vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
car_name VARCHAR (50) NOT NULL -- you name your car?
);
First of all, fields are not anything like columns and rows are not
anything like records! No wonder you were using the word "merge" in
this posting -- you are locked into a file system model of data, not an
RDBMS.
Next, a data element has one and only one name in a schema. It does
not change from table to table. But file systems do not have a data
dictionary, so you missed this basic point.
Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.
Of course; this design has no data integrity. You cannot trust
anything you get out of it. This attempt at kludging an unusable
design only showed you a FEW of the problems you have.
Start over. What are the attributes of a car? The VIN is the natural
key. But why does a car have a name? Does it come when you call it?
And even if it have a name, why is it soooooo long?
You need a table of vehicle users (they are entities, aren't they?
Model them!).
You need a table of vehicle assignments (or access rights) It will
reference the Cars and the Users, but also have a date range, the users
role (driver, passenger), etc.
You used over-sized columns -- Why did you pick BIGINT and VARCHAR(50)?
They will only accumulate garbage. You have no DRI actions. YOu have
no constraint.
Oh, and remember to do a full data audit to clean up what you have in
the DBMS now.