CODEWITHSUNDEEP
mysqlsqlif-statementsql-updatesql-insert
asdf
asdf

Reputation: 29

if insert works then

so im trying to check if a table entry is equal to another table entry and if it is update the table and if it is not put a new entry in

im getting errors at the if exists? Ive tried moving the begins and ends around to no avail.

BEGIN    
    IF EXISTS(
        SELECT  *    
        FROM x, y    
        WHERE x.ID = y.ID
)
BEGIN
    INSERT INTO x(ID, sID,cID, n, a)
    VALUES(y.ID, y.sID, y.cID, NULL, NULL)
END
BEGIN
    ELSE
        UPDATE x 
        SET x.sID= y.sID, x.cID= y.cID
    END  
END
;

Upvotes: 0

Views: 31

Answers (1)

GMB
GMB

Reputation: 222492

I think that you want the insert ... on duplicate key syntax:

insert into x(id, sid, cid, n, a)
select y.id, y.sid, y.cid, null, null
from y
on duplicate key update x.sid = y.sid, x.cid = y.cid

This query attempts to insert all existing rows in y in table x. When an already existing id is met, the query goes to the on duplicate key clause, and updates sid and cid on the existing row.

For this to work, you need a unique (or primary key) constraint on x(id).

Upvotes: 1

Related Questions