How to use a INSERT and a WHERE clause in the same query[MYSQL]?

Question

Pseudocode:
If(userid != 2 && friendid != 1)
{
userid=1
friendid=2
}

Current SQL query(not working):
INSERT INTO friendrequests (userid, friendid) VALUES (1,2) SELECT * FROM friendrequests WHERE userid <> 2 AND friendid <> 1

Answer 1

Though I don't understand the reason behind inserting the data into the same table ... Should it be an Update than the Insert...

However, here's how you could achieve...



Approach # 1

---------------------

INSERT FriendRequests (userid, friendid)

SELECT

UserIdValue=CASE WHEN UserId<>2 AND FriendId<>1 THEN 1 ELSE UserId,

FriendIdValue=CASE WHEN UserId<>2 AND FriendId<>1 THEN 2 ELSE FriendId

FROM FriendRequests



Approach # 2

---------------------

INSERT FriendRequests (userid, friendid)

SELECT 1,2 FROM friendrequests WHERE userid <> 2 AND friendid <> 1

UNION ALL

SELECT userid ,friendid FROM friendrequests WHERE userid = 2 AND friendid = 1



The above approaches are meant for inserting the data... and operations of both are pretty self explanatory.



Now, if your real quest was to Update -



UPDATE friendrequests

SET UserId=CASE WHEN UserId<>2 AND FriendId<>1 THEN 1 ELSE UserId,

FriendId=CASE WHEN UserId<>2 AND FriendId<>1 THEN 2 ELSE FriendId



Tell us if you have trouble understanding or implementing it.



--In 'thoughts'...

Lonely Rogue.

https://twitter.com/LonelyRogue

Answer 2

INSERT INTO friendrequests (userid, friendid)

SELECT userid, friendid FROM friendrequests WHERE userid <> 2 AND friendid <> 1



This should select records from the select statement and insert into the same table. I guess in real situation, you wouldn't grab something from a table and insert into the same table, but you get the idea.

Answer 3

in your pseudo code looks like you are updating userid and friendid, if so



UPDATE friendrequests

SET userid=1,friendid=2

WHERE userid<>2 and friendid<>1

Answer 4

Your SQL code doesn't make a lot of sense...either give a VALUES clause or a subquery, not both. Furthermore, your subquery looks suspect...if you eleminated the VALUES clause, it would select everey existing row from friendrequests that meets your WHERE criteria and then try inserting them back into the table, duplicating all those rows. Excatly what were you trying to accomplish here?

Answer 5

on your first section you carry out 2 queries are they mandatory. From what i'm able to inform you examine if the variable is empty whether it relatively isn't any longer your delete from the database have been their username and dvd tournament. then you pass directly to question a 2nd time to work out if it replaced into efficient. you do no longer would desire to question the database to work out if a question replaced into efficient. if (!empty($deleteFilm)) { $sqlString = "DELETE FROM orders the place dvd = '$deleteFilm' and shopper = '$usrName';"; if ($queryResult = @mysqli_query($connection, $sqlString)) { $msg = "the object has been bumped off out of your orders."; header("region: memberOrders.Hypertext Preprocessor?msg=$msg"); go out; } might grow to be if (!empty($deleteFilm)) { $sqlString = "DELETE FROM orders the place dvd = '$deleteFilm' and shopper = '$usrName';"; if ($sqlString == real) { $msg = "the object has been bumped off out of your orders."; header("region: memberOrders.Hypertext Preprocessor?msg=$msg"); go out; } _______________________________________... $sqlString = "DELETE FROM orders the place shopper = '$usrName';"; on the top of this line you have 2 ";" you only want a million additionally i visit look into this as i do no longer know in the experience that your telling it what to delete. your only sayign delete from this table have been shopper = "" i do no longer know in the experience that your lacking a * image