CODEWITHSUNDEEP
mysqlc#-4.0
Matt
Matt

Reputation: 1582

SQL INSERT or UPDATE

Following on from this: SQL INSERT from SELECT and the correct answer marked there.

I will need to be able to also check whether the row already exists, also by using the username. So would I delete and then insert or is there a better way? And if it is delete, how do I say DELETE FROM table WHERE UserID = do the username select here Thanks

Upvotes: 1

Views: 164

Answers (3)

Ervin
Ervin

Reputation: 2452

If delete then you can use:

DELETE a FROM Avatar a LEFT JOIN User u ON a.UserID=u.UserID WHERE u.UserName='theusername'

Upvotes: 2

Mike Sherov
Mike Sherov

Reputation: 13427

Coming from the other question where you're doing an "insert from select", I assume you want to not insert the row that already have entries for the keys you're attempting to insert. I also assume that it's giving you some error like "duplicate key found".

With those assumptions in mine, the fix is simple: add the IGNORE keyword after INSERT, so you're query looks something like this:

INSERT IGNORE... //rest of query

Upvotes: 0

Borealid
Borealid

Reputation: 98559

Try REPLACE INTO instead of INSERT INTO. If the UserID is specified and is the primary key for the table, this will overwrite the row whose UserID matches what you insert.

To answer your sub-question, it would be DELETE FROM table WHERE UserID IN (SELECT UserID ...)

Side note: StackOverflow is really not an appropriate venue for learning basic SQL. If you read up first, your questions will be better and the answers correspondingly more useful.

Upvotes: 0

Related Questions