mysql在where子句中加载数据

发布于 2024-09-11 02:18:27 字数 257 浏览 2 评论 0原文

我需要根据某些条件使用加载数据文件更新表中的现有行，这可能吗？

load data infile 'E:/xxx.csv'
into table tld_tod
@aaa, @xxx_date, @ccc
fields terminated by ','
 LINES TERMINATED BY '\r\n'
set xxx = str_to_date(@xxx_date, '%d-%b-%y')
where xxx is not null and aaa=@aaa

原文

I need to update existing rows in table with load data infile based on some condition, is this possible?

load data infile 'E:/xxx.csv'
into table tld_tod
@aaa, @xxx_date, @ccc
fields terminated by ','
 LINES TERMINATED BY '\r\n'
set xxx = str_to_date(@xxx_date, '%d-%b-%y')
where xxx is not null and aaa=@aaa

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

白龙吟 2024-09-18 02:18:27

您还可以创建临时表，将 CSV 文件中的数据插入到临时表中，最后通过所需的操作和过滤将数据插入到目标表中。

CREATE TEMPORARY TABLE staging LIKE tld_tod;

LOAD DATA INFILE 'E:/xxx.csv'
INTO TABLE staging
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

INSERT INTO tld_tod
SELECT STR_TO_DATE(col_date, '%d-%b-%y') AS date
WHERE col_date IS NOT NULL;

You ca also create a staging table, insert the data from the CSV file into the staging table and then finally insert the data into your target table with the required operations and filtering.

CREATE TEMPORARY TABLE staging LIKE tld_tod;

LOAD DATA INFILE 'E:/xxx.csv'
INTO TABLE staging
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

INSERT INTO tld_tod
SELECT STR_TO_DATE(col_date, '%d-%b-%y') AS date
WHERE col_date IS NOT NULL;

回复收藏 0 原文

无畏 2024-09-18 02:18:27

在 MySQL 中，可以在更新之前创建触发器。所以在这种情况下我建议使用：

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON table
       FOR EACH ROW
       BEGIN
           IF NEW.xxx IS NOT NULL THEN
               SET NEW.xxx = 0;
           END IF;
       END;//
delimiter ;

创建触发器后，您可以运行 load data infile 而无需 WHERE。
我不确定您的具体要求条件是什么，但请在 BEGIN 和 END 内执行。

In MySQL it's possible to create triggers before update. So in this case I suggest to use:

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON table
       FOR EACH ROW
       BEGIN
           IF NEW.xxx IS NOT NULL THEN
               SET NEW.xxx = 0;
           END IF;
       END;//
delimiter ;

After creating trigger, you can run load data infile without WHERE.
I'm not sure what's your specific required condition, but do it inside BEGIN and END.

回复收藏 0 原文

~没有更多了~