January 9, 2019 at 5:21 am
Hi everyone
I have a table which needs to be updated by another temporary table, and I'm having problems doing this.
The table is called MAILINGPREFERENCE and the particular column that needs updating is called MAILINGTYPE (this is a primary key on the MAILINGPREFERENCE table).
The temporary table, TEMPADDU25B, contains the columns SERIALNUMBER and MAILINGTYPE. SERIALNUMBER is also a Primary Key on the MAILPREFERENCE table.
I need to get the MAILINGTYPE column from the TEMPADDU25B table into the MAILINGPREFERENCE.MAILINGTYPE column. It's possible that other MAILINGTYPE fields may already exist in the MAILINGPREFERENCE table, but if that's the case then these new ones need to be added. I'm using the following SQL, but it's showing as 0 rows affected. Could someone please help me?
Many thanks
INSERT INTO mailingpreference (mailingtype,serialnumber)
SELECT DISTINCT
a.mailingtype
,a.serialnumber
FROM TEMPADDU25B as a
WHERE a.serialnumber is not null
AND NOT EXISTS(SELECT 1
FROM mailingpreference as b
WHERE a.mailingtype=b.mailingtype
AND b.serialnumber=a.serialnumber
)
January 9, 2019 at 5:27 am
j.clay 47557 - Wednesday, January 9, 2019 5:21 AMHi everyoneI have a table which needs to be updated by another temporary table, and I'm having problems doing this.
The table is called MAILINGPREFERENCE and the particular column that needs updating is called MAILINGTYPE (this is a primary key on the MAILINGPREFERENCE table).
The temporary table, TEMPADDU25B, contains the columns SERIALNUMBER and MAILINGTYPE. SERIALNUMBER is also a Primary Key on the MAILPREFERENCE table.
I need to get the MAILINGTYPE column from the TEMPADDU25B table into the MAILINGPREFERENCE.MAILINGTYPE column. It's possible that other MAILINGTYPE fields may already exist in the MAILINGPREFERENCE table, but if that's the case then these new ones need to be added. I'm using the following SQL, but it's showing as 0 rows affected. Could someone please help me?
Many thanks
INSERT INTO mailingpreference (mailingtype,serialnumber)
SELECT DISTINCT
a.mailingtype
,a.serialnumber
FROM TEMPADDU25B as a
WHERE a.serialnumber is not null
AND NOT EXISTS(SELECT 1
FROM mailingpreference as b
WHERE a.mailingtype=a.mailingtype
AND b.serialnumber=a.serialnumber
)
This query is never going to UPDATE anything. All that it will do is INSERT rows which are deemed 'missing' (according to the NOT EXISTS() part of the query).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply