SQL update from one Table to another based on a ID match IN db2

by

DB2 does indeed support joins in an UPDATE statement, only not the way you think — DB2 follows the SQL ANSI standard:

UPDATE
     Sales_Import SI
 SET
    Sales_Import.AccountNumber = (
      SELECT 
        RAN.AccountNumber
      FROM
        RetrieveAccountNumber RAN
      WHERE  
        SI.LeadID = RAN.LeadID
    )

The above assumes that LeadID uniquely identifies records in RetrieveAccountNumber, otherwise you will get an error because the subquery would return more than one row.

Edit:

To address comments below, if no matching record in RetrieveAccountNumber can be found, Sales_Import.AccountNumber will be set to null. If this is undesirable, one could use COALESCE() to assign a default value.

More Related Contents:

  1. DB2 Comma Separated Output by Groups
  2. An unexpected token “CREATE TRIGGER
  3. MySQL/SQL: Update with correlated subquery from the updated table itself
  4. Equivalent of LIMIT for DB2
  5. PL SQL UPDATE STATEMENT
  6. How can I do an UPDATE statement with JOIN in SQL Server?
  7. MySQL, update multiple tables with one query
  8. How do I make an UPDATE while joining tables on SQLite?
  9. MySQL error code: 1175 during UPDATE in MySQL Workbench
  10. updating table rows in postgres using subquery
  11. how can I Update top 100 records in sql server
  12. Return pre-UPDATE column values using SQL only
  13. SQL UPDATE order of evaluation
  14. What’s the difference between findAndModify and update in MongoDB?
  15. Relationship between catalog, schema, user, and database instance
  16. Does DB2 have an “insert or update” statement?
  17. Use SELECT inside an UPDATE query
  18. What are differences between INSERT and UPDATE in MySQL?
  19. I want to use CASE statement to update some records in sql server 2005
  20. How to split a string value based on a delimiter in DB2
  21. Update a column of a table with a column of another table in PostgreSQL
  22. Spring boot Hibernate error “Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set” when working with multiple data sources
  23. Update query using Subquery in Sql Server
  24. How to create and insert a JSON object using MySQL queries?
  25. Query validation using C#
  26. The best way to remove value from SET field?
  27. Error: Column does not exist in postgresql for update [duplicate]
  28. DB2 Query to retrieve all table names for a given schema
  29. SQL Update woes in MS Access – Operation must use an updateable query
  30. Update a table with data from another table

Leave a Comment