Sqlite和Mysql和SqlServer中insert … select … where not exist的用法

时间:2022-05-14 05:20:18

下面介绍Mysql和Sqlite和Sqlserver中,根据select的条件判断是否插入。例如:

一、Mysql中:

  INSERT INTO books (name) SELECT 'SongXingzhu' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

二、Sqlite中:

  由于Sqlite中没有临时表:dual

  所以,需要这样写

  INSERT INTO books (name)  SELECT 'Songxingzhu'  WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

三、Sqlserver中:

  SqlServer中需要另一种写法:

  IF NOT EXISTS (SELECT id FROM books WHERE id = 1)  INSERT INTO books (name) SELECT 'Songxingzhu'