Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Update values based on non-matched records

Update values based on non-matched records

Scheduled Pinned Locked Moved Database
helpdatabasetutorialannouncement
5 Posts 2 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • E Offline
    E Offline
    empulse
    wrote on last edited by
    #1

    I have been trying to make this work, but so far no luck. Probably an easy one for someone with a bit more experience. Here is a simplified explanation. I have two tables, the primary table is an inventory table listing items identifed by a unique inventory number and the second is a record of transactions. A record is added to the second table when a transaction occurs that affects an item in the first table. For instance when an item is sold, a transaction is created in the transaction table - "Sold" designated by the letter "S", a new item received is designated with the letter "R" etc. Occasionally an item is listed as "received" that is already in the inventory table or an item is listed as "sold" that is was not entered in the inventory table. I need to do two things: (1) Find the records in the second table (t_log) that do not have a matching inventory number in the Inventory table and insert appropriate text into a comment field. (2) find the records in the transaction table with 'R' transaction type and insert an appropriate comment in the transaction table. In each case I need to set a boolean to to True (error). The tables look like this: ---------------- Inventory Table (Inv) ----------------------- InventoryNo Name, etc. ------------------- TransactionLog (T_Log) ----------------------- InvNo TransactionType Comment Error (Boolean) ------------------ For the records that are in the T_Log but not in the inventory (Table A), the query below displays the records that I want to update, but I don't know how to update just those records. (select Inv.InventoryNo,Inv.ItemName,T_Log.id, T_Log.Invno,t_log.transactiontype, t_log.commment FROM Inv RIGHT OUTER JOIN T_Log on InventoryNo = Invno WHERE Inv.InventoryNo is NULL ) Thanks for any help

    M 1 Reply Last reply
    0
    • E empulse

      I have been trying to make this work, but so far no luck. Probably an easy one for someone with a bit more experience. Here is a simplified explanation. I have two tables, the primary table is an inventory table listing items identifed by a unique inventory number and the second is a record of transactions. A record is added to the second table when a transaction occurs that affects an item in the first table. For instance when an item is sold, a transaction is created in the transaction table - "Sold" designated by the letter "S", a new item received is designated with the letter "R" etc. Occasionally an item is listed as "received" that is already in the inventory table or an item is listed as "sold" that is was not entered in the inventory table. I need to do two things: (1) Find the records in the second table (t_log) that do not have a matching inventory number in the Inventory table and insert appropriate text into a comment field. (2) find the records in the transaction table with 'R' transaction type and insert an appropriate comment in the transaction table. In each case I need to set a boolean to to True (error). The tables look like this: ---------------- Inventory Table (Inv) ----------------------- InventoryNo Name, etc. ------------------- TransactionLog (T_Log) ----------------------- InvNo TransactionType Comment Error (Boolean) ------------------ For the records that are in the T_Log but not in the inventory (Table A), the query below displays the records that I want to update, but I don't know how to update just those records. (select Inv.InventoryNo,Inv.ItemName,T_Log.id, T_Log.Invno,t_log.transactiontype, t_log.commment FROM Inv RIGHT OUTER JOIN T_Log on InventoryNo = Invno WHERE Inv.InventoryNo is NULL ) Thanks for any help

      M Offline
      M Offline
      Mycroft Holmes
      wrote on last edited by
      #2

      Your select should woth with a LEFT outer join Also try this SELECT * FROM t_log WHERE invno NOT IN (SELECT Inventryno from inv) AND transactiontype = 'R/S'

      Never underestimate the power of human stupidity RAH

      E 1 Reply Last reply
      0
      • M Mycroft Holmes

        Your select should woth with a LEFT outer join Also try this SELECT * FROM t_log WHERE invno NOT IN (SELECT Inventryno from inv) AND transactiontype = 'R/S'

        Never underestimate the power of human stupidity RAH

        E Offline
        E Offline
        empulse
        wrote on last edited by
        #3

        It's not the select that I am having trouble with it is how to update the transaction log (comments and error)

        M E 2 Replies Last reply
        0
        • E empulse

          It's not the select that I am having trouble with it is how to update the transaction log (comments and error)

          M Offline
          M Offline
          Mycroft Holmes
          wrote on last edited by
          #4

          I always test an update with a select to see what the results are before I commit to the update, saves restoring a databse if I get the filter wrong. So naturally I assume everyone does this, you'll note the R/S needs to be modified as well naturally. UPDATE t_log SET Comment = 'Thinking is good for you' --SELECT * FROM t_log WHERE invno NOT IN (SELECT Inventryno from inv) AND transactiontype = 'R/S'

          Never underestimate the power of human stupidity RAH

          1 Reply Last reply
          0
          • E empulse

            It's not the select that I am having trouble with it is how to update the transaction log (comments and error)

            E Offline
            E Offline
            empulse
            wrote on last edited by
            #5

            OK This did it for part one. UPDATE T_Log set Commment='Not in main table' WHERE invno NOT IN (SELECT Inventoryno from Inv) AND transactiontype = 'T' Thanks

            1 Reply Last reply
            0
            Reply
            • Reply as topic
            Log in to reply
            • Oldest to Newest
            • Newest to Oldest
            • Most Votes


            • Login

            • Don't have an account? Register

            • Login or register to search.
            • First post
              Last post
            0
            • Categories
            • Recent
            • Tags
            • Popular
            • World
            • Users
            • Groups