Created
December 12, 2023 08:10
-
-
Save sqlparser/a2d6395edf52e26205dc92a5ec3683c6 to your computer and use it in GitHub Desktop.
Informix Sample SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
```sql | |
-- Informix sample sql | |
CREATE VIEW myview (cola, colb) AS | |
SELECT colx, coly from firsttab | |
UNION | |
SELECT colx, colz from secondtab; | |
CREATE VIEW palo_alto AS | |
SELECT * FROM customer WHERE city = 'Palo Alto' | |
WITH CHECK OPTION | |
; | |
MERGE INTO t2 AS o USING t1 AS n ON o.f1 = n.f1 | |
WHEN NOT MATCHED THEN INSERT ( o.f1,o.f2) | |
VALUES ( n.f1,n.f2) | |
; | |
INSERT INTO t2(f1, f2) | |
SELECT t1.f1, t1.f2 FROM t1 | |
WHERE NOT EXISTS | |
(SELECT f1, f2 FROM t2 | |
WHERE t2.f1 = t1.f1); | |
MERGE INTO sale USING new_sale AS n | |
ON sale.cust_id = n.cust_id | |
WHEN MATCHED THEN UPDATE | |
SET sale.salecount = sale.salecount + n.salecount | |
WHEN NOT MATCHED THEN INSERT (cust_id, salecount) | |
VALUES (n.cust_id, n.salecount); | |
MERGE INTO customer c | |
USING ext_customer e | |
ON c.customer_num=e.customer_num AND c.fname=e.fname AND c.lname=e.lname | |
WHEN NOT MATCHED THEN | |
INSERT | |
(c.fname, c.lname, c.company, c.address1, c.address2, | |
c.city, c.state, c.zipcode, c.phone) | |
VALUES | |
(e.fname, e.lname, e.company, e.address1, e.address2, | |
e.city, e.state, e.zipcode, e.phone) | |
WHEN MATCHED THEN UPDATE | |
SET c.fname = e.fname, | |
c.lname = e.lname, | |
c.company = e.company, | |
c.address1 = e.address1, | |
c.address2 = e.address2, | |
c.city = e.city, | |
c.state = e.state, | |
c.zipcode = e.zipcode, | |
c.phone = e.phone ; | |
UPDATE nmosdb@wnmserver1:test | |
SET name=(SELECT name FROM test | |
WHERE test.id = nmosdb@wnmserver1:test.id) | |
WHERE EXISTS( | |
SELECT 1 FROM test WHERE test.id = nmosdb@wnmserver1:test.id | |
); | |
UPDATE orders | |
SET ship_charge = | |
(SELECT SUM(total_price) * .07 FROM items | |
WHERE orders.order_num = items.order_num) | |
WHERE orders.order_num = 1001; | |
``` |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment