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

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

insert to mysql table

what is the syntax to insert a record only if the record doesn't already exist?
say its based on a column named 'testname'
0
dgrafx
Asked:
dgrafx
  • 3
  • 2
3 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
If you have a unique index on the table that's not an autonum you can use insert ignore

Insert ignore into tablename values (col1,col2, ...)
0
 
dgrafxAuthor Commented:
the uniqueness is determined by a combo of userid and testname

i need the syntax
the code i would use on sql server is appearing to fail

what i'd normally use is something like
INSERT INTO sometbl
(x,y)
SELECT 'x','y'
WHERE NOT EXISTS (Select 1 from sometbl where x='x' and y='y')

thanks
0
 
Kevin CrossChief Technology OfficerCommented:
As aarontomosky indicated, MySQL has the concept of INSERT IGNORE which will do insert normally unless there is a conflict which the IGNORE specifies to skip. If the two columns are not a key, you can just do EXACTLY as you did with MS SQL in MySQL. The trick in MySQL is that when using a WHERE clause you need a table. You can use FROM DUAL like in Oracle.

INSERT INTO sometbl(x,y)
SELECT 'x','y'
FROM DUAL
WHERE NOT EXISTS (Select 1 from sometbl where x='x' and y='y')
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Aaron TomoskyTechnology ConsultantCommented:
You can have a unique across multiple columns. Cleaner and integrity is guaranteed. And inserts will be faster. .
0
 
dgrafxAuthor Commented:
ok - thanks a lot guys
i will test it later - i cant right now
0
 
dgrafxAuthor Commented:
thanks a lot guys
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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