CODEWITHSUNDEEP
sqlsql-serverdatabasejoininner-join
Reply not
Reply not

Reputation: 311

Select and Update with join giving different records

I have two table with relation

There is a field in assignment table which is status. So I am fetching record from table assignment where status is complete that returns 6909 records

Than I return records using inner join with condition of status complete that also return 6909 records.

But when i update table using join and with same condition that update 6625 records not 6909 . Is there is any problem with query or anything else? Kindly guide

Queries:

First : return 6909 records

SELECT * FROM [ITSC].[dbo].[assignment] where assignment_status = 'Completed'

Second : return 6909 records

SELECT tickets.ticket_submitted_by , tickets.ticket_type , tickets.ticket_open_date , tickets.ticket_priority , tickets.ticket_description , assignment.staff_name,assignment.assigned_time_start,assignment.assigned_time_end,assignment.assignment_status
from tickets 
inner join 
assignment 
on tickets.ticket_id = assignment.ticket_id
where assignment.assignment_status = 'Completed'

Third : Update 6625 records not 6909

 update tickets set tickets.ticket_close_date = '2015-04-29' 
,tickets.ticket_close_time = '2015-04-29 09:25:40.670' from tickets
 inner join assignment
 on tickets.ticket_id = assignment.ticket_id
 where assignment.assignment_status = 'Completed'

Upvotes: 2

Views: 61

Answers (1)

Anson Aricatt
Anson Aricatt

Reputation: 393

Can you try this query 

 update tickets set tickets.ticket_close_date = '2015-04-29' 
                    ,tickets.ticket_close_time = '2015-04-29 09:25:40.670' 
    from assignment
    where  tickets.ticket_id = assignment.ticket_id
    and  assignment.assignment_status = 'Completed'

Upvotes: 1

Related Questions