Select if exist else insert?
Your statement is fine as it is. Only problem is, you can't use it like a normal query. Control structures like IF
or WHILE
are only allowed in stored procedures or functions.
Just create a procedure like this:
delimiter $$
create procedure select_or_insert()
begin
IF EXISTS (select * from users where username = 'something') THEN
select id from users where username = 'something';
ELSE
insert into users (username) values ('something');
END IF;
end $$
and call it like this:
call select_or_insert();
That's it.
UPDATE if exists else INSERT in SQL
The below query will fulfill your requirement.
INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001
if exists update else insert mysql
You can use Store Procedures to solve this problem like below
DELIMITER $$
DROP PROCEDURE IF EXISTS `select_or_insert`$$
CREATE PROCEDURE select_or_insert()
begin
IF EXISTS (SELECT * FROM test WHERE id="id") THEN
SELECT * FROM test WHERE id="id";
ELSE
INSERT INTO test (id,name) VALUES ("id",'name');
END if;
END$$
DELIMITER ;
and call it like this:
call select_or_insert();
SQL Server IF EXISTS THEN 1 ELSE 2
If you want to do it this way then this is the syntax you're after;
IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName ='xxxxxxxx')
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 2
END
You don't strictly need the BEGIN..END
statements but it's probably best to get into that habit from the beginning.
SQL - IF EXISTS UPDATE ELSE INSERT INTO
Create a
UNIQUE
constraint on yoursubs_email
column, if one does not already exist:ALTER TABLE subs ADD UNIQUE (subs_email)
Use
INSERT ... ON DUPLICATE KEY UPDATE
:INSERT INTO subs
(subs_name, subs_email, subs_birthday)
VALUES
(?, ?, ?)
ON DUPLICATE KEY UPDATE
subs_name = VALUES(subs_name),
subs_birthday = VALUES(subs_birthday)
You can use the VALUES(col_name) function in the UPDATE clause to
refer to column values from the INSERT portion of the INSERT ... ON
DUPLICATE KEY UPDATE - dev.mysql.com
- Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.
Related Topics
SQL Server Script to Create a New User
How to Select and Update Rows at the Same Time
Using SQL Count in a Case Statement
SQL Return Only Duplicate Rows
How to Get Week Start and End Date String in Postgresql
SQL to Query Text in Access with an Apostrophe in It
How to Remove Leading and Trailing Quotes in SQL Server
Combine Varchar Column with Int Column
Is a One Column Table Good Design
Which Is Better: Bookmark/Key Lookup or Index Seek
When to Use an Enum or a Small Table in a Relational Database
How to Write Subquery Inside the Outer Join Statement
Where Col1,Col2 in (...) [SQL Subquery Using Composite Primary Key]