Unanswered: Update all null data in a table where columns match
Hi i have posted this somewhere else but have had little response so trying to broaden my help request.
I have a small problem where i have 2 seperate tables
Table 1 (tstockout)
Table 2 (tRemoved)
In Table 1
Field Names: Date, Time, PartNo, Qty, Phase, Line, Machine
There could be 10 items in the table.
In Table 2
Field Names: Phase, Line, Machine
There will only be 1 row of data in this table
At present the Phase, Line, Machine are not used in Table 1, however i would like to change this to log whatever data is stored in Table 2's fields.
Date Time Part Qty Phase Line Machine
Record 1...1/6/10, 14:30, 1234, 4, Null, Null, Null
Record 2...1/6/10, 14:35, 2234, 1, Null, Null, Null
Record 3...1/6/10, 14:38, 1334, 2, Null, Null, Null
Record 4...1/6/10, 14:39, 1134, 1, Null, Null, Null
Phase Line Machine
Record 1... Phase 1, Line 1, Machine 1
Record 1...1/6/10, 14:30, 1234, 4, Phase 1, Line 1, Machine 1
Record 2...1/6/10, 14:35, 2234, 1, Phase 1, Line 1, Machine 1
Record 3...1/6/10, 14:38, 1334, 2, Phase 1, Line 1, Machine 1
Record 4...1/6/10, 14:39, 1134, 1, Phase 1, Line 1, Machine 1
I understand that there is different ways to set the system up, however i am just trying to get this task done ASAP.
I guess i need to use an update query but have no idea how to get it to do what i want.
Based on the little you have said it is not simply that this could be set up "differently" but that it is set up wrong. It is also important to understand that in a relational database there is no such thing as "record 1". "record 2", "record 3" etc - rows do not have ordinal positions.
However - you ask for an update statement that will work.
Assuming that your data is an accurate representation of your problem this will work:
Run this only if you are happy with it and only on test data. It takes no account of nulls since there are no non-nulls in your data. It does not "update data where columns match" since there are no columns in the sample data to match.
I think this is an example where abstracting the problem creates more issues than it solves. Using an imaginary schema, and so converting to and from the the actual schema, adds an unnecessary additional layer of complexity.
Fundamentally, troubleshooting is actually about eliminating variables not introducing them.