Update a table with join?

by

It is not possible to do this with a JOIN. The Firebird UPDATE statement has no FROM clause. The syntax is:

UPDATE {tablename | viewname} [[AS] alias]
   SET col = newval [, col = newval ...]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]
   [RETURNING <values>]

<m>, <n>     ::=  Any expression evaluating to an integer.
<values>     ::=  value_expression [, value_expression ...]
<variables>  ::=  :varname [, :varname ...]

However the equivalent of your example query is:

update INVOICE_ITEMS 
set DISCOUNT = 3 
WHERE EXISTS (SELECT 1 FROM ITEM_PRICE_QUNTITY B WHERE B.ID = ITEM_PRICE_NO)
AND INVOICE_ID = 33

If you want to update using data from additional tables, you might want to consider using MERGE. In your comment you ask for the equivalent query to do the following with Firebird:

UPDATE B 
SET B.QUANTIY = b.QUANTIY + a.QUANTITY 
FROM ITEM_PRICE_QUNTITY B JOIN INVOICE_ITEMS A ON A.ITEM_PRICE_NO = B.ID 
WHERE A.INVOICE_ID = 33

The equivalent MERGE statement would be:

MERGE INTO ITEM_PRICE_QUNTITY AS B
    USING INVOICE_ITEMS AS A
    ON A.ITEM_PRICE_NO = B.ID AND A.INVOICE_ID = 33
    WHEN MATCHED THEN
        UPDATE SET B.QUANTIY = B.QUANTIY + A.QUANTITY

More Related Contents:

  1. Which one of these Queries are wrong and which is correct (SQL) [closed]
  2. SQL Selecting from three tables
  3. How to cast the DateTime to Time
  4. Inner join vs Where
  5. Computed / calculated / virtual / derived columns in PostgreSQL
  6. Generate a resultset of incrementing dates in TSQL
  7. Find a string by searching all tables in SQL Server
  8. Xcode 4 and Core Data: How to enable SQL Debugging
  9. Database Design for Revisions?
  10. How to use parameters “@” in an SQL command in VB
  11. JPQL IN clause: Java-Arrays (or Lists, Sets…)?
  12. Finding similar strings with PostgreSQL quickly
  13. Why use a JOIN clause versus a WHERE condition?
  14. Call dynamic SQL from function
  15. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  16. Slow simple update query on PostgreSQL database with 3 million rows
  17. How do I alter the position of a column in a PostgreSQL database table?
  18. How to export image field to file?
  19. Move SQL data from one table to another
  20. How to unpivot in BigQuery?
  21. Get the last day of the month in SQL
  22. Nested select statement in SQL Server
  23. How to convert PostgreSQL 9.4’s jsonb type to float
  24. Find rows that have the same value on a column in MySQL
  25. Referencing current row in FILTER clause of window function
  26. SQL take just the numeric values from a varchar
  27. Why are foreign keys more used in theory than in practice?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Challenge,how to implement an algorithm for six degree of separation?
  30. Drop column from SQLite table

Leave a Comment