Updating a record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating a record

Author

Topic

sdr
Starting Member

8 Posts

Posted - 2008-10-21 : 14:03:49

I am trying to Update records in one table using information from another table. The query I have now does a Union on two tables to get records when someone works overtime, undertime or is out for the day. I need to Update the data in the Unioned table from a field in a third table. The query below does my Union to create a table but I am not sure how to go about updating the Comment field with the Comment field from my third table.

SELECT Records.Employee, Records.TimeDate AS ExceptionDate, TimeExc.ExceptionOK, TimeExc.Comment, TimeExc.Manager, TimeExc.CreateDate,
TimeExc.MaintDate, Records.HoursWorked
FROM (SELECT employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
AS HoursWorked
FROM (SELECT EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
FROM dbo.Time_Record RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
AS TimeRecords
GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name LEFT OUTER JOIN
dbo.TimeExceptions AS TimeExc ON EmpMaster.employee_account_name = TimeExc.Employee AND Records.TimeDate = CONVERT(varchar(10),
TimeExc.ExceptionDate, 101)
WHERE (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (EmpMaster.employee_account_name = @emp) AND
(Records.HoursWorked > @overtime OR
Records.HoursWorked < @undertime)
UNION
SELECT Employee, ExceptionDate, ExceptionOK, Comment, Manager, CreateDate, MaintDate, '0' AS Expr1
FROM dbo.TimeExceptions AS TExcp
WHERE (CONVERT(varchar(10), ExceptionDate, 101) NOT IN
(SELECT CONVERT(varchar(10), start_date_time, 101) AS Expr1
FROM dbo.Time_Record AS TR
WHERE (employee_account_name = @emp) AND (start_date_time >= @sdt) AND (start_date_time <= @edt))) AND (Employee = @emp) AND
(ExceptionDate <= @edt) AND (ExceptionDate >= @sdt)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 14:18:04

You could make your unioned query a view and then join with the third table.
update table3 set comment='whatever'
from view_unions
where <put your join conditions>

Or you could use the entire unioned query as it is instead of the view.
update table3 set comment='whatever'
from
(select * from table1
union
select * from table2)Unioned
where <put your join conditions>

sdr
Starting Member

8 Posts

Posted - 2008-10-21 : 14:58:18

Thanks sakets, here is what I have now. If you don't mind would you look over it to see if it looks like it will work properly. I would hate to run it and have it overwrite all the existing comments that were in place other than the ones it is supposed to overwrite...

UPDATE exceptions set Comment = dbo.pink_slips.comments
FROM
(SELECT Records.Employee, Records.TimeDate AS ExceptionDate, TimeExc.ExceptionOK, TimeExc.Comment, TimeExc.Manager, TimeExc.CreateDate,
TimeExc.MaintDate, Records.HoursWorked
FROM (SELECT employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
AS HoursWorked
FROM (SELECT EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
FROM dbo.Time_Record RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
AS TimeRecords
GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name LEFT OUTER JOIN
dbo.TimeExceptions AS TimeExc ON EmpMaster.employee_account_name = TimeExc.Employee AND Records.TimeDate = CONVERT(varchar(10),
TimeExc.ExceptionDate, 101)
WHERE (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (EmpMaster.employee_account_name = @emp) AND
(Records.HoursWorked > @overtime OR
Records.HoursWorked < @undertime)
UNION
SELECT Employee, ExceptionDate, ExceptionOK, Comment, Manager, CreateDate, MaintDate, '0' AS Expr1
FROM dbo.TimeExceptions AS TExcp
WHERE (CONVERT(varchar(10), ExceptionDate, 101) NOT IN
(SELECT CONVERT(varchar(10), start_date_time, 101) AS Expr1
FROM dbo.Time_Record AS TR
WHERE (employee_account_name = @emp) AND (start_date_time >= @sdt) AND (start_date_time <= @edt))) AND (Employee = @emp) AND
(ExceptionDate <= @edt) AND (ExceptionDate >= @sdt)) AS exceptions
WHERE (TimeDate = (SELECT CONVERT(varchar(10), start_date_time, 101) AS PinksDate
FROM dbo.pink_slips)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 15:57:51

If you don't have any backup or whatever and want to test.
Alter Table exceptions add test_Comment varchar(255 (or whatever...))

Now you can run your update on that new column and see in test_Comment if it's OK or not.

Webfred

Planning replaces chance by mistake

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-21 : 16:33:39

The syntax looks ok. Not sure if you have the functionality right.
Incase the dataset isn't huge, a crude way of checking for updates is to make the update inside a transaction.
Rollback the transaction if it doesn't look right. Commit it if it does.

begin tran

update table set comment='whatever'

check for results

rollback /commit

sdr
Starting Member

8 Posts

Posted - 2008-10-22 : 09:28:34

Yea my functionality is not correct. I don't actually have it returning any data from the dbo.pink_slips table to be able to update the field in my unioned table. I have been looking over it and so far I have not been able to figure out how to get the data in the dbo.pink_slips.comments where it equals the exception date to be able to update with.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources