Column prefix does not match

[dom24]

HI,

I'm using the following query:

SELECT * FROM Passwords
INNER JOIN Staff ON passwords.managerno = staff.managerno
UPDATE passwords SET passwords.managerno = staff.managerno
WHERE passwords.managerno<>staff.managerno

But i'm getting the error message:

The column prefix 'staff' does not match with a table name or alias name used in the query.

Any ideas? Doesn't make sense to me as I do have a table named Staff.

 

[lupins46]

The query is wrong in two ways.

The syntax is wrong for an update query.
You need something like...

Update t1 inner join t2 on t1.id=t2.id
Set ......

But it is also wrong logically.
You cannot have a join
t1.id=t2.id

and a Where clause
t1.id <>t2.id

They are completely contradictory.

You should explain what you want to do and what your tables are and then someone may be able to help.

 

[dom24]

I tired it this way:

UPDATE passwords
INNER JOIN Staff ON passwords.managerno = staff.managerno
SET passwords.managerno = staff.managerno
WHERE passwords.managerno<>staff.managerno

but kept getting an "incorrect syntax near keyword 'INNER'"
message.

Anyway, I've got a staff table which holds the managerno and a Passwords table which holds the managerno and password.
These tables are joined by managerno.
When the staff table is updated with new details, I want to check the staff.managerno with the password.managerno and if there are any in the staff table that are not in the password table I want it to insert them.

 

[mordja]

dom24,

You still have conflicting criteria as lupins46 pointed out
passwords.managerno = staff.managerno and passwords.managerno<>staff.managerno together.
And if I understand you right you want to add a new managerno to passwords if that managerno is in staff but not passwords.


INSERT INTO passwords ( managerno )
SELECT staff.managerno
FROM staff
WHERE (((staff.managerno) Not In (select managerno from passwords)));



Hope this helps

Mordja

 

[lupins46]

INSERT INTO Passwords (ManagerNo )
SELECT Staff.ManagerNo
FROM Staff LEFT JOIN Passwords ON Staff.Managerno = passwords.mangerno
WHERE passwords.mangerno Is Null;

 

[PHV]

Take a look at INSERT INTO ... SELECT ... WHERE NOT EXISTS (...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244