Hi
I have two tables with the same structur: called DISPLAYLOAYOUT and DISPLAYLAOUT1 :
CREATE TABLE CARDBA.DISPLAYLAYOUT - DL
(
AREACODE VARCHAR2(5 BYTE) NOT NULL,
ZONECODE VARCHAR2(5 BYTE) NOT NULL,
LOCATIONCODE VARCHAR2(5 BYTE) NOT NULL,
RECTORIGINX NUMBER,
RECTORIGINY NUMBER,
RECTWIDTH NUMBER,
RECTHEIGHT NUMBER,
RECTDESTX NUMBER,
RECTDESTY NUMBER,
TXTORIGINX NUMBER,
TXTORIGINY NUMBER,
TXTWIDTH NUMBER,
TXTHEIGHT NUMBER,
TXTDESTX NUMBER,
TXTDESTY NUMBER
)
CREATE TABLE CARDBA.DISPLAYLAYOUT1 - DL1
(
AREACODE VARCHAR2(5 BYTE) NOT NULL,
ZONECODE VARCHAR2(5 BYTE) NOT NULL,
LOCATIONCODE VARCHAR2(5 BYTE) NOT NULL,
RECTORIGINX NUMBER,
RECTORIGINY NUMBER,
RECTWIDTH NUMBER,
RECTHEIGHT NUMBER,
RECTDESTX NUMBER,
RECTDESTY NUMBER,
TXTORIGINX NUMBER,
TXTORIGINY NUMBER,
TXTWIDTH NUMBER,
TXTHEIGHT NUMBER,
TXTDESTX NUMBER,
TXTDESTY NUMBER
)
AREACODE, ZONECODE, LOCATIONCODE make up the primary key
I'm new to sql, what I would like to do is this:
match DL1 with DL with the pk
if no row exists then insert row from DL1 into DL
if the row exists then update all other rect and txt fields (all other fields) in DL with those values in DL1
Can anyone help me? Your help would be much appreciated
Thanks in advance
Privs
5 2350
Hi
I'm using this:
MERGE INTO CARDBA.DISPLAYLAYOUT A
USING (SELECT AREACODE,
ZONECODE,
LOCATIONCODE,
RECTDESTX,
RECTDESTY,
RECTHEIGHT,
RECTORIGINX,
RECTORIGINY,
RECTWIDTH,
TXTDESTX,
TXTDESTY,
TXTHEIGHT,
TXTORIGINX,
TXTORIGINY,
TXTWIDTH
FROM CARDBA.DISPLAYLAYOUT1) b
ON (A.AREACODE = B.AREACODE
AND A.ZONECODE = B.ZONECODE
AND A.LOCATIONCODE = B.LOCATIONCODE)
WHEN NOT MATCHED
THEN
INSERT (AREACODE,
ZONECODE,
LOCATIONCODE,
RECTDESTX,
RECTDESTY,
RECTHEIGHT,
RECTORIGINX,
RECTORIGINY,
RECTWIDTH,
TXTDESTX,
TXTDESTY,
TXTHEIGHT,
TXTORIGINX,
TXTORIGINY,
TXTWIDTH)
VALUES (b.AREACODE,
b.ZONECODE,
b.LOCATIONCODE,
b.RECTDESTX,
b.RECTDESTY,
b.RECTHEIGHT,
b.RECTORIGINX,
b.RECTORIGINY,
b.RECTWIDTH,
b.TXTDESTX,
b.TXTDESTY,
b.TXTHEIGHT,
b.TXTORIGINX,
b.TXTORIGINY,
b.TXTWIDTH)
WHEN MATCHED
THEN
UPDATE SET a.RECTDESTX = b.RECTDESTX,
A.RECTDESTY = B.RECTDESTY,
A.RECTHEIGHT = b.RECTHEIGHT,
A.RECTORIGINX = b.RECTORIGINX,
A.RECTORIGINY = b.RECTORIGINY,
A.RECTWIDTH = b.RECTWIDTH,
A.TXTDESTX = b.TXTDESTX,
A.TXTDESTY = b.TXTDESTY,
A.TXTHEIGHT = b.TXTHEIGHT,
A.TXTORIGINX = b.TXTORIGINX,
A.TXTORIGINY = b.TXTORIGINY,
A.TXTWIDTH = b.TXTWIDTH;
but i get this error: Error at line 2
ORA-30926: unable to get a stable set of rows in the source tables
It looks like your source table returns more than one row matched to your target table. You will need to make sure that your where clause contain all the necessary joins in order to do the merge operation.
thanks, I seem to have removed the duplicate rows and it worked
Thank you sooo much
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jn |
last post by:
I have a page that shows info from MySQL. It is for an online education
site. It uses different classes arranged in a hierarchy. Each lesson is
roughly laid out like this:
Lesson
|-Intro
|...
|
by: Jenny |
last post by:
Hi!
I wonder how to use conditions in the inserted table(in a
insert/update) trigger? The inserted table contain all the rows that
have been updated or inserted (for an update/insert trigger),...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to...
|
by: Lisa |
last post by:
I have an asp.net website with an Access Database that works great on
my local server, but when I migrated to our production server, none of
my sql processes works (i.e update, insert, delete) Does...
|
by: luna |
last post by:
trying to create an update/insert button
basically if the record exists in the database i want to do an update
if it doesnt exist i want to create a new record using insert
all using the same...
|
by: Daniel |
last post by:
update/insert with microsoft.xml.xquery(.net2.0beta)
can anyone provide an example of how to update/insert with
microsoft.xml.xquery?
|
by: dev |
last post by:
Hi Everyone, I'm a little confused with the .NET 2.0 BindingSource for
WinForms. Is it possible to update, insert or delete database items
through the BindingSource with a Typed data object as its...
|
by: kareemaffan |
last post by:
Hello Everyone
I want to insert values into MS Access database through VB.NET in Visual Studio 2005 . I have used the following code which is not working.
Private Sub Button2_Click(ByVal sender...
|
by: sridhar4554 |
last post by:
How to give a grant like select,update,insert on a single table
i had tried with the command grant eg:GRANT SELECT,INSERT,UPDATE
ON DTS_OUTBOX_QUEUE
TO sanpapps@dap1
IDENTIFIED BY 'disable';...
|
by: mwenz |
last post by:
I am trying to update an Access table using OLEDB in VB.Net 2005. I can add rows but I cannot update them.
Code to instantiate the Access database and table...
Dim conn As New...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |