SQL join problem (MS SQL Server)

pickarooney

I'm trying to do the following which, to my mind should be simple enough, but I've been told it's not possible with SQL:

It's from a billing system where the table tillticketline contains the individual lines of a recipt and tillticket the total of these lines, employee is the employee who made the sale.
There are some errors in the system whereby employees who no longer work there are appearing on some ticket lines after their departure dates. I know aht caused this and have fixed it for the future, but there are a number of outstanding tickets where the wrong employee is listed. For all these ticket lines, I want to put the boss' name instead. Her ID is 32.

My theory was to update all the ticket lines in the database, changing the seller to the boss wherever the departure date of the currently listed employee was later than the date of the ticket line. The command goes through with no error message but doesn't have the desired effect, and running just the select part returns now rows. Any ideas?

update tillticketline set idseller=32 where exists (select * from tillticketline inner join employee on tillticketline.idseller = employee.id where tillticketline.datetime > employee.depdate)

seamus

Don't know why this would work any better, but try:

update tillticketline set idseller=32 where idseller IN (select idseller from tillticketline inner join employee on tillticketline.idseller = employee.id where tillticketline.datetime > employee.depdate)

Try it on a copy of the database first! :eek:

Beano

Maybe I'm missing something but i cant the Ticketline table that is being updated is related to the TicketLine table in the exists. Shouldnt you just be doing something like :

update tillticketline
set idseller=32
from tillticketline
inner join employee on tillticketline.idseller = employee.id
where tillticketline.datetime > employee.depdate

Beano

Seamus just beat me to the punch. Mine and his should do the same thing. MIne is more efficient though

The advice to try it on a copy of the database is advice you should take heed of.

pickarooney

Seamus' one is drastic - all ticket lines from all sellers returned by the select statement are attributed to the boss, regardless of the date of the ticket.

I don't really understand Beano's syntax, though it looks quite tidy. I'll try it on another copy of the database tomorrow.

Thanks for the input!

seamus

Ah.
I did make one small error:

update tillticketline set idseller=32 where tillticketline.id IN (select id from tillticketline inner join employee on tillticketline.idseller = employee.id where tillticketline.datetime > employee.depdate)

This is getting the list of tickets that are wrong (as opposed to a list of employee who have wrong tickets) :eek:

Hence the need for a copy of the database

pickarooney

Yep, should be fine, with one small adjustment (and employee.depdate >0) as all active employees' departure date is set as 0, otherwise it takes every single sale from the current staff!

Cheers

pickarooney

I get "Ambiguous column name 'ID'" now (in the select statement), but changing it to select tillticketline.id seems to work.