Talk:Merge (SQL)

Merge

Recommend merging UPDATE, INSERT, DELETE, MERGE and SELECT statements into DML article and redirecting these statements there. Comments? SqlPac 05:18, 17 May 2007 (UTC)

POLICY DEBATE: Use of source code and other examples in articles

Someone started a policy debate concerning the use of source code in articles, that seems to directly impact this (and possibly other Database articles). See below for details:

I have opened a debate on the use of source code and other examples in Wikipedia articles. It seems that many pieces of example source code etc. currently in Wikipedia violate Wikipedia policy, so we need to either clarify or change the situation. Depending on the result of the discussion, this may result in a number of source code examples being summarily removed from computing articles!

Please reply there, not here, if you wish to contribute.—greenrd 10:50, 18 May 2007 (UTC)

Thanks.SqlPac 15:10, 18 May 2007 (UTC)

Replace?

Isn't the

INSERT ...

ON DUPLICATE KEY UPDATE

statement the more compatible one?

Syntax (MySQL-Page)

Gtred 07:16, 16 August 2007 (UTC)

in the artcle should be Upsert command --83.190.87.3 03:22, 6 November 2007 (UTC)

The MySQL ON DUPLICATE KEY UPDATE construct is nonstandard. SQL:2008 does not have that, but it does have MERGE. Jackrepenning (talk) 01:26, 9 February 2012 (UTC)

It has been suggested that Upsert be merged into this article or section. (Discuss)

Dont you mean "It has been suggested that Merge be upserted into this article or section."? —Preceding unsigned comment added by 90.155.123.114 (talk) 16:19, 11 February 2011 (UTC)

The article only discusses using MERGE for "upsert." Doesn't the SQL:2008 MERGE have more uses than this? Jackrepenning (talk) 01:27, 9 February 2012 (UTC)

score so far: 1 LOL ! 76.21.148.50 (talk) 08:12, 31 August 2011 (UTC)

Usage

Syntax description has "table_name" and "table_reference" but the text paragraph discusses "Source" and "Target" tables. Which is which? — Preceding unsigned comment added by Davidbak (talk • contribs) 15:24, 2 December 2011 (UTC)

Clarified Source and Target, based on reading of Oracle and DB2 MERGE statement documentation -- Rgauf (talk) 23:18, 13 March 2012 (UTC)

Contradiction

This page claims Merge was introduced in SQL:2008. SQL:2003 claims it was introduced in 2003. --Ysangkok (talk) 14:07, 13 March 2012 (UTC)

The SQL:2003 page is correct. MERGE was an Optional component in the SQL:2003 Foundation standard, as noted in Oracle 10g Database SQL Reference documentation [here|http://docs.oracle.com/cd/B13789_01/server.101/b10759/ap_standard_sql001.htm#g15079] where 10g partially supports Optional Feature F312 - MERGE statement. MERGE was updated for SQL:2008, as noted in Oracle 11g Database SQL Reference documentation [here|http://docs.oracle.com/cd/E14072_01/server.112/e10592/ap_standard_sql004.htm] where 11g does not support some of the newer clauses.

I am updating the MERGE page appropriately. Rgauf (talk) 23:13, 13 March 2012 (UTC)

Proposed Content

If this page will continued to be forwarded from [upsert] and if code examples will stay in database articles, the following might be helpful:

With version 9.1 of PostgreSQL the implementation of "Common Table Expressions" also known as "WITH clauses" has been extended. The following code will perform an "upsert" on table "destination" taking data from table "source" which contain of a reference key "refkey" and two data columns "data1", and "data2"

WITH upsert AS (
	UPDATE destination us SET 
		data1 = us.data1,
		data2 = us.data2
	FROM
		source us
	WHERE
		ud.islandkey = us.islandkey
	RETURNING ud.*
	)
INSERT INTO destination (refkey, data1, data2)
	SELECT
		s.refkey,
		s.data1,
		a.dat2
	FROM
		source s
	WHERE
		s.islandkey NOT IN ( SELECT n.islandkey FROM upsert n)
	;

This can be generalized by using database functions.

Reference:
PostgreSQL Documentation: http://www.postgresql.org/docs/9.1/static/queries-with.html
SQL:1999

78.94.127.4 (talk) 10:13, 10 July 2012 (UTC)