Here is a query that I am attempting to run. This query takes the asigned shift out of one table and copies it into another table. However when I run this query the proper row is updated but all the other fields in that column go to a NULL. So it is doing all fields in that column not just the field in the row that I want.
fpmi.db.runUpdateQuery(“UPDATE interim SET assigned_shift = (SELECT x.ProdShiftNum FROM ProdEmployee x WHERE ProdEmployeeID = %s AND shift = %s AND mach_num = %s)” % (assigned_operator, shift, mach_num), “mocrosoft”)
Red flag goes up for the approach. You shouldn’t need to copy data from one table to another. A relational database allows you to create other tables that link the data in such a way that this shouldn’t be necessary.
I don’t know why it would set other columns to NULL. I did notice that the UPDATE portion of your query is updating every row (since it doesn’t have a WHERE clause) to a single value returned from the sub-select query.
I understand the red flag comment, just dont fully understand the thing with databases yet. All I know is I have two databases, one data base has the employee list in it. This list is more like a view, then I have the data base that stores what the supervisors do on the assignements screen. The query I have takes the employee information from the employee data base and also the data from a new database we are designing called production jobs, then using this data the supervisor will do assignements and the proper rows and columns will be populated. What this query was meant to do was put the employees regularly assigned shift into the database. This way if a person is on day shift but is a second shift employee we can track certain things that MGMT has choose to. Hopefully this make sence.
[quote=“nathan”]Red flag goes up for the approach. You shouldn’t need to copy data from one table to another. A relational database allows you to create other tables that link the data in such a way that this shouldn’t be necessary.
I don’t know why it would set other columns to NULL. I did notice that the UPDATE portion of your query is updating every row (since it doesn’t have a WHERE clause) to a single value returned from the sub-select query.[/quote]
I think what nathan is trying to say is that you don’t ever need to copy data in a database. You should have a table for the employees with their assigned shifts, and another table that records the employee used during a specific shift. Then to find out if the employee worked the right shift, your query will JOIN the two tables for the info you need.
For example, say you have a a table ‘employees’ that has the employee name, ID and assigned_shift. Then another table ‘shifts’ that lists the date, shift and employee_ID of the person that worked it. Then to get a list of the people that worked shifts they shouldn’t have, you can do the following:
SELECT e.name, e.ID, e.assigned_shift, s.shift
FROM shifts s
LEFT JOIN employees e ON e.ID = s.employee_ID
WHERE e.assigned_shift != s.shift
This will take every row in the ‘shifts’ table and glue each ‘employees’ row that has the same employee ID to the end of it (and then only shows the rows where the two shift fields don’t match).
With this JOIN query, you don’t need to copy the employees assigned shift into the shifts table. If the employees assigned shift can change, you may need to do more (like add an end date column in the employee table and make a new entry for that person every time their assigned shift changes), but the idea is the same.
Haha, poor Martin… I’ll try to save you a bit of re-explaining…
I think This Post might explain a bit to everyone coming along about why he’s trying to do this.
Thanks Colby, I added some posting to this message thread to hopefully clear things up even further. Just a friendly reminder this is the first time I have ever worked with SCADA/HMI packages so I am doing my best to learn as much as I can. And I wish to thank all those who have lended so much assistance and guidance.
[quote=“Colby.Clegg”]Haha, poor Martin… I’ll try to save you a bit of re-explaining…
I think This Post might explain a bit to everyone coming along about why he’s trying to do this.[/quote]
If you already understand this, forgive me for being simplistic…
Your SQL update statement above doesn’t “copy” anything in the sense that no new rows are added to a table and/or no row is moved from one table to another.
From your update statement, I surmise you simply want to change the value in the “assigned_shift” column of the “interim” table. The value you want written to the “assigned_shift” column is the “ProdShiftNum” for the employee with the specified “ProdEmployeeID”, “shift”, and “mach_num” from the “ProdEmployee” table.
As the guys stated above, your update statement will change the “assigned_shift” value for every row in the “interim” table because there is no “where” clause. SQL server will only update rows for which the “where” clause is true. If no explicit “where” clause is given, it will update every row.
If there is only one row in the “interim” table and you don’t specify a where clause, this will likely not be a problem. But if there are multiple rows in the “interim” table and you only want to update one row, not specifying a “where” clause is going to cause you some problems.
With all that said, there is nothing obvious in your update statement that would appear to cause other columns in the table to be changed to NULL values. It is possible (but I don’t know how likely) that there is a trigger on the table that executes some logic in the background that changes the other columns. The trigger would not be obvious to you unless you are the one who defined and/or manages the table. I guess an appropriate question is, “Who is responsible for defining and managing the ‘interim’ table?” Did you set up the table?
Yes it took some time but i realized that i needed an extra WHERE. I guess i was not thinking straight when I wrote the query. i knew what i wanted to do and well in my office there are two people one is a staunch dem and one a staunch rep and it is like living with the bickersons (hopefully that wont show my age) hahahaha. And sometimes i get caught up in it and loose concentration. that along with being such a novice on Sql just makes it that much more difficult. Thanks again.
I’ll try to save you a bit of re-explaining…