[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

insert else update

insert into table2.varcharColumn1,table2.vacharColumn2 values table1.varcharColumn1,1 where table2.varcharColumn1 does not exist

else
update table2.varcharColumn1 set table2.vacharColumn2=1

I only want to insert new values in this new table
0
rgb192
Asked:
rgb192
  • 3
  • 3
  • 2
  • +2
7 Solutions
 
lwadwellCommented:
If your varcharColumn1 is primary key or has a unique index on it ... use INSERT ... ON DUPLICATE KEY UPDATE refer: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
0
 
Jesus RodriguezIT ManagerCommented:
insert into table2(varcharColumn1,vacharColumn2)
select varcharColumn1,'1'
from table1
where varcharColumn1 NOT IN (SELECT VARCHARCOLUMN1 FROM TABLE2)
0
 
rgb192Author Commented:
insert into table2(varcharColumn1,vacharColumn2)
select varcharColumn1,'1'
from table1
where varcharColumn1 NOT IN (SELECT VARCHARCOLUMN1 FROM TABLE2)


where is else update



I can not find exact example
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
thehagmanCommented:
I assume that varcharcolumn2 is to count the number of occurances. In that case

insert into table2(varcharColumn1,varcharColumn2)
select varcharColumn1,1 from table1 
on duplicate key update varcharcolumn2 = varcharcolumn2+1;

Open in new window

and varcharcolumn2 should rather be of type integer.
If you simply want to ignore duplicates use

insert ignore into table2(varcharColumn1,varcharColumn2)
select varcharColumn1,1 from table1; 

Open in new window

0
 
Jesus RodriguezIT ManagerCommented:
THIS IS THE INSERT INTO TABLE 2 WHERE THE FIELD FROM TABLE 1DOES NOT APPEAR ON  TABLE 2

insert into table2(varcharColumn1,vacharColumn2)
select varcharColumn1,'1'
from table1
where varcharColumn1 NOT IN (SELECT VARCHARCOLUMN1 FROM TABLE2)


UPDATE TABLE 2
SET varcharColumn2=1
WHERE VARCHARCOLUMN1 IN (SELECT VARCHARCOLUMN1 FROM TABLE1)
0
 
lwadwellCommented:
given you example INSERT - I have fixed syntax and added the ON DUPLICATE
insert into table1 (varcharColumn1, vacharColumn2)
  values (<varcharColumn1_value>,1)
   ON DUPLICATE KEY UPDATE vacharColumn2=1;

Open in new window

I replaced table1.varcharColumn1 with a place-holder for a value/variable ... when using INSERT .. VALUES a table column reference does not make sense.  I however you were doing an INSERT .. SELECT, then:
insert into table1 (varcharColumn1, vacharColumn2)
  SELECT table1.varcharColumn1,1 FROM table1
   ON DUPLICATE KEY UPDATE vacharColumn2=1;

Open in new window

0
 
tliottaCommented:
Since this is also in Databases Miscellaneous, in most current DB2 versions, you'd possibly use a MERGE statement. It can UPDATE or DELETE existing rows, or INSERT rows.

Tom
0
 
rgb192Author Commented:
>>ON DUPLICATE KEY UPDATE vacharColumn2=1;

table2 does not have varcharcolumn1 as a key

because I got error while creating
0
 
lwadwellCommented:
My very first comment started with "If your varcharColumn1 is primary key or has a unique index on it" ... that is because for ON DUPLICATE KEY to work a primary key OR an unique index must exist so that MySQL can understand what is a duplicate.

Your original question stated "where table2.varcharColumn1 does not exist" ... this means that you intend table2.varcharColumn1 to be unique.  Add a unique index on that column, i.e.
    CREATE UNIQUE INDEX table2_uidx ON table2 (varcharColumn1)
0
 
rgb192Author Commented:
thanks
for
insert update
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now