Problem with Update query

SQL Apprentice Question
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.



Celko Answers
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?
);



>> These were joined using the a_cid to c_id field [sic], .. <<


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.



>> but now I want to copy the tblAccess.a_user [sic] into the relative record [sic] in the tblCars.c_user table [sic] <<


Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.


>> But I get an error advising the subquery returns more than 1 result. <<


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.


>> Could anyone please help? <<


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.