duanqiao1961 2016-06-24 09:58
浏览 117
已采纳

将数据从一个表复制到另一个表,但添加第二个WHERE子句不会插入数据

I have two tables. When A user clicks a button, it will copy data from table 1 to table 2 where the doesn't already exist in table 2.

I had the query working just fine, until I added a new column in the first table named "onsite" - which is either set to yes, or it is NULL.

Here is the query I've tried. It no longer inserts ANY data to the past_bidder table (table 2)

  1. $copybidderquery = "INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created)
  2. SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created
  3. FROM bidders
  4. WHERE (bidfname, bidlname, bidphnum, bidlicense, bidaddress)
  5. NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders)
  6. AND onsite != 'yes'";

I've also tried moving the where onsite != 'yes' to right after "WHERE" and it still does the same thing.

  1. $copybidderquery = "INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created)
  2. SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created
  3. FROM bidders
  4. WHERE onsite != 'yes'
  5. AND (bidfname, bidlname, bidphnum, bidlicense, bidaddress)
  6. NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders)";

As stated, this query works just fine without the "onsite != 'yes'" line.

I think it's just some little syntax error I'm overlooking. Any help would be appreciated.

  • 写回答

3条回答 默认 最新

  • doushitang4276 2016-06-24 10:05
    关注
    1. INSERT INTO past_bidders(bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created)
    2. SELECT bidnum, bidfname, bidlname, bidphnum, bidlicense, bidaddress, bidtaxexempt, bidtaxid, date_created
    3. FROM bidders
    4. WHERE onsite is NULL AND (bidfname, bidlname, bidphnum, bidlicense, bidaddress)
    5. NOT IN (SELECT bidfname, bidlname, bidphnum, bidlicense, bidaddress FROM past_bidders)
    6. ;

    Check following example when you say not equal to something it will not return the null values

    1. mysql> select * from calls;
    2. +----+------------+---------+
    3. | id | date | user_id |
    4. +----+------------+---------+
    5. | 1 | 2016-06-22 | 1 |
    6. | 2 | 2016-06-22 | NULL |
    7. | 3 | 2016-06-22 | NULL |
    8. | 4 | 2016-06-23 | 2 |
    9. | 5 | 2016-06-23 | 1 |
    10. | 6 | 2016-06-23 | 1 |
    11. | 7 | 2016-06-23 | NULL |
    12. +----+------------+---------+
    13. 7 rows in set (0.00 sec)
    14. mysql> select * from calls where user_id!=1;
    15. +----+------------+---------+
    16. | id | date | user_id |
    17. +----+------------+---------+
    18. | 4 | 2016-06-23 | 2 |
    19. +----+------------+---------+
    20. 1 row in set (0.00 sec)
    21. mysql> select * from calls where user_id is null;
    22. +----+------------+---------+
    23. | id | date | user_id |
    24. +----+------------+---------+
    25. | 2 | 2016-06-22 | NULL |
    26. | 3 | 2016-06-22 | NULL |
    27. | 7 | 2016-06-23 | NULL |
    28. +----+------------+---------+
    29. 3 rows in set (0.00 sec)

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 syri可视化不显示插入缺失
  • ¥30 运行软件卡死查看系统日志分析不出来
  • ¥15 C语言代码改正特征选择算法设计,贝叶斯决策,,设计分类器,远程操作代码修正一下
  • ¥15 String 类valuve指向的问题
  • ¥15 在ros2的iron版本进行编译时遇到如下问题
  • ¥18 vs用setup project打包项目实现安装完立即运行
  • ¥15 孟德尔随机化TwoSampleMR在线提取结局数据,遇到Error in check_reset(override_429)的问题
  • ¥15 ONNX转RKNN遇到问题
  • ¥60 以太网电缆未接通怎么处理
  • ¥15 关于超声图片进行放射组学的疑问
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部