How to do conditional INSERT in MySQL?

Question

I have got two tables.

assets table

   |  AID  | NAME | 
   | 1234  | item1|

item table

   |  BID  | ITEM | 
   |       |      |

Question: How do I insert a data into item table only when the same data I want to insert is present in assets table? or is it even possible? Say I can only insert into item table BID value(1234) only when assets table AID value(1234) is present. I tried doing this:

INSERT INTO 'item'('BID') VALUES (1234) WHERE 'item'.BID='assets'.AID

What should be inserted as ITEM value then? 'item1' string? Also, do you want this 'WHERE' check applied for a single operation only, or you want to copy that assets table as whole? — raina77ow
– raina77ow, Commented Oct 16, 2012 at 23:14

Jon Armstrong - Xgc · Accepted Answer · 2012-10-17 00:49:13Z

1

INSERT INTO item (BID) SELECT assets.aid FROM assets WHERE aid = 1234;

Use DISTINCT in the SELECT if you may find multiple matches and don't want more than one row inserted.

Also, use a foreign key constraint if you want to just restrict the insert in the database. Once you have that, you don't need to conditionally insert. Just insert and let the database fail the attempt if the aid is not already present in the parent table.

answered Oct 17, 2012 at 0:49

Jon Armstrong - Xgc

2642 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shomz · Accepted Answer · 2012-10-16 23:10:42Z

0

You can't use WHERE with the INSERT statement as mysql doesn't support it. Just remove the WHERE part and it should work.

But you'll have to check separately whether the data exist in the other table (or within a sub-query).

answered Oct 16, 2012 at 23:10

Shomz

37.7k4 gold badges61 silver badges87 bronze badges

1 Comment

Theron Chong Over a year ago

But what i want is that only when the value AID(primary key) is present in the assets table then only I can insert the same data into item table. I have no idea how to go about checking using two tables

Yatrix · Accepted Answer · 2012-10-16 23:54:07Z

0

You could use dynamic SQL and execute it later, but that's ugly. Just do an IF conditional. If the record exists in Value, run an insert, else, do not.

Try something like this:

(SELECT Count(*) FROM Value WHERE BID = '1234') > 0
    --your insert--

edited Oct 16, 2012 at 23:54

answered Oct 16, 2012 at 23:41

Yatrix

13.9k17 gold badges57 silver badges83 bronze badges

Collectives™ on Stack Overflow

How to do conditional INSERT in MySQL?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related