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

    sqlite3 - UPDATE a table from the SELECT of another one -

    I have the first table article where some data is missing. In the table, articles_tmp I have a part of the missing data. So I want to update the articles. Where the path path is data in articles_tmp.path where the path is not zero . With SQLite , it does not work: update article SET path = (SELECT at.path FROM Articles_tmp as AS, WHERE at.article_id = a.id and a.asp, the text is in the form and not on. It has been transmitted) where the path is null MS Access Along with, it works fine: Update Articles Join the Inner Articles at articles_tmp articles.id = articles_tmp.id SETP articles.path = articles_tmp.path Can anyone help? Sin CE SQL Server Syntax, which I provided, was not working, here this solution With whom I have come. A little modification of your original query, but with my "correlation" suggestion added. There is a SQL Bella for this: Here is the code: update article SET path = (SELECT path from articles_tmp AS T WHERE t.id
    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

    javascript - Render HTML after each iteration in loop -

    I'm trying to gradually increase the font size of text on a web page. The code works in my place, although the new HTML / CSS does not render after every iteration of the loop and when all this is done, then display only the text of 100 px size. To see the text as if it is slowly zooming, I need to do this JavaScript is down because it is being used from a different file here is what I have ... < Pre> & lt; P class = "game-title" style = "font-size: 50px" & gt; Test & lt; / P & gt; Function sleep (milliseconds) {var start = new date (). GetTime (); For (var i = 0; i & lt; 1e7; i ++) {if ((new date). GetTime () - start) & gt; Milliseconds {break; CSS ('font-size', 'parseInt ($ (' game-title '). CSS (' font-size ')) + 1 + "pixels"); } While (parasont ($ ('# text'). Css ('font-size')) & lt; = 100) {sleep (1000); IncreaseSize (); Using CSS: Use of jQuery (sample):
    Read more