SQL Server update statement with join -


I have data in a load table and try to write an update statement to populate an additional self-referenced table. I'm doing, but I'm having trouble with the exact syntax.

Here is a simple example of two table layouts and simultaneous data: 

  create table tmpLoad (status number VARCHAR (2), supervisor number VARCHAR (2)) tmpLoad values ​​( '01', '00') tmpLoad values ​​( '02', '01') tmpLoad values ​​( '03 put', '01') put in place to put tmpLoad value ('04, '03 '(', '05 03 ') CREATE TABLE tmpPosition (PositionID integer, PositionNumber VARCHAR (2), putting SupervisorID integer) into tmpPosition value (1 ") into tmpLoad value' 01 ', nULL) tmpPosition value (2 , '02', faucet) are tmpPosition values ​​(e.g. 3, '03' faucet) (,, 4 '04 'Tap' INSERT tmpPosition value insert into the tmpPosition value (, 5 '05', null)

Data pole in TM As a unique identifier, And their respective supervisors represent five employees using their PositionNumber .

  • The employee is 01 employee ( supervisor number == 0)
  • report to employee 02 and 03 staff 01
  • employee 04 and 05

    • tmpPosition table is automatically referenced, where status ID can be in several other records' Supervisor ID column.

    As you can see, the supervisor ID column is currently zero for all records, together with the appropriate PositionID in both of these tables I'm trying to populate it.

    To verify my idea, I have the following SELECT query: 

      a.PositionNumber, a.SupervisorNumber, b.PositionID, b .PositionNumber, b.SupervisorID involved in selecting a.SupervisorNumber = the tmpLoad b.PositionNumber a tourist tmpPosition b

    produced the desired results like that to me, it seemed: 

      Situation - Supervisor Number - StatusId - Status Report - Supervisor 02 -------------- 01 ---------------- 1 ----------- 01 ------------ - Faucet 03 -------------- 01 -------- -------- 1 ----------- 01-- ------------ Faucet 04 ------------- - 03 ---------------- 3 ---- ------- 03 -------------- Tap 05 -------------- 03 ----------- ----- 3 - ---------- 03 -------------- Tap

    From this, I assumed supervisor ID Columns will be populated with value from PositionID > column for these four records when I ran the following update query: 

      update combinations based on a a.SupervisorNumber from tmpPosition set SupervisorID = b.PositionID tmpLoad tmpPosition b = B.PositionNumber

    However, since I run the query was not the result I expected It was: 

      SELECT * FROM tmpPosition PositionID - PositionNumber -SupervisorID 1 ----------- 01 -------------- 1 2 ----------- 02 ------- ------- Faucet 3 ----------- 03 -------- ------ 3 4 ----------- 04- ------------- Faucet 5 ----------- 05 - ------------ Null

    will be the ideal results:

    StatusId - StatusNumber - Supervisor 1 ------ ----- 01 ----------- --- Faucet 2 ----------- 02 -------------- 1 3 ----------- 03 ----- --------- 1 4 ----------- 04 ---------- ---- 3 5 ----------- 05 -------------- 3

    What is happening here and I SupervisorID field to the status ID as described in this scenario?

    Try it ... 

      Update tmpPosition SET SupervisorID = the tL.SupervisorNumber tmpPosition tp JOIN TmpLoad tl oN tp.PositionNumber = CONVERT (INT, tl.PositionNumber)

    this is exactly what you want in this schema. Yet I do not think this is a good DB design. 

      update tmpPosition set SupervisorID = case when tl.SupervisorNumber = 0 and then minus tl.SupervisorNumber end tmpPosition include tmpLoad tl on tp tp which .PositionNumber = CONVERT (INT, tl.PositionNumber)

    -

    Comments

    Post a Comment

    Popular posts from this blog

    Member with no value in F# -

    After I am trying to convert F # to the following C # code: [StructLayout (LayoutKind.Sequential, charset = CharSet.Ansi)] Private class DOCINFOA {[MarshalAs (UnmanagedType.LPStr)] public string nomeDocumento; [Marshall (UnmanagedType.LPStr)] Public string arquivoSaida; [Marshall (UnmanagedType.LPStr)] Public String Tipodo; } I am doing the following: Named place printer module RawPrinterHelper = [& LT; StructLayout (LayoutKind.Sequential, charset = CharSet.Ansi) & gt;] Type DOCINFOA = [& LT; MarshalAs (UnmanagedType.LPStr) & gt;] Member this.nomeDocumento [& LT; MarshalAs (UnmanagedType.LPStr) & gt;] Member this.arquivoSaida [& LT; MarshalAs (UnmanagedType.LPStr) & gt;] Member This Tipoda [/ code> But I get: One type of definition is necessary for one or more members . Can I vacate these members? you val code> member : [& lt; StructLayout (LayoutKind.Sequential, charset = CharSet.Ansi) & gt;] Type DOCINFOA = [...
    Read more

    java - Joda Time Interval Not returning what I expect -

    मेरे पास निम्न प्रोग्राम है import java.util। *; Import java.text। *; आयात करें org.joda.time। *; सार्वजनिक श्रेणी के स्कोप कंट्रोल {सार्वजनिक स्थिर अंतराल मिलनसार () {दिनांक समय currDate = नया दिनांक समय (2008, 4, 4, 15, 30, 0, 0); दिनांक समय epochDate = नया दिनांकटाइम (2000, 1, 1, 12, 0, 0, 0); अंतराल अंतराल = नया अंतराल (युरोप डेट, करोडेट); वापसी अंतराल; } सार्वजनिक स्थिर शून्य मुख्य (स्ट्रिंग [] आर्ग्स) {डबल दिनबात = मिलते समय ()। ToDurationMillis () / 1000/60/60/24; StdOut.println (daysBtween); }} मुझे आउटपुट मिल रहा है: 3016.0 लेकिन जो मैं देख रहा हूं वह है: 3016.1458333333 मैं क्या कर रहा हूँ ? toDurationMillis एक लंबा लौटा देता है, और प्रत्येक प्रभाग को int के रूप में घोषित किया जाता है। जावा इस प्रकार इनट्स को लॉन्ग में परिवर्तित कर देगा और डिवीजन को लंबे समय तक लौटाना होगा। परिणाम को अंत में दोहरे रूप में परिवर्तित करना। डबल्स का उपयोग करके अभिव्यक्ति करने के लिए जावा को बताने के लिए, अभिव्यक्ति के किसी भी घटक को दोहरे रूप में घोषित करें। उदाहरण के लिए: ...
    Read more

    c# - Showing a SelectedItem's Property -

    So I have enabled a rightclick option for my DataGrid. I want to display the only chosen property but it is not behaving like what I would like it to display my name and additional. Public category Paymentinfo {public int PaymentNo {get; Set; } Public string date {get; Set; } Public Double Pay {get; Set; } Public Double Principle {get; Set; } Public Double Interest {get; Set; } Public Double Balance {Received; Set; }} Private Zero MenuItem_OnClick (Object Sender, RoutedEventArgs e) {MessageBox.Show (AmortGrid.SelectedItem.ToString ()); } I'm trying to apply it without using a View Module! If I place a breakpoint where the messagebox and the recipient place it on selected ITEM, then this property will display the payment. A date-payment-principle-interest-balance is the only value that needs payment. Was hoping that it would be something like this MessageBox.Show (AmortGrid.SelectedItem.PaymentNo.ToString ()); When you call ToString () So you get the class typ...
    Read more