How can I update three columns in table A from three columns in table B in one UPDATE statement?
For this question, you can use the power of relational algebra.
looks like a simple case but I'm confused: table_A column - date_A , primary key key_A table_B column - date_B, foreign key key_B I need to update every date_A with date_B.
I need to update each row of table_A with matching row from table_B. I don't mean to be *** about it but it may confuse others who's trying to search before posting their problem.
VEUC_END_DATE CURRENT DATE) -- AND EXISTS WHERE EXISTS (SELECT 1 FROM DBQ2.
UPDATE summary_data SET current_category = (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id) WHERE EXISTS (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id); If you want to test your skills using the SQL UPDATE statement, try some of our practice exercises.
From the link to the other thread I was still unable to find solution. cheers fynn Fynn, I'm with you 100% about Famudba's post.
date_A = table_B.date_B ) would work because "select MAX(date_B) from table_B" returns one record, where as "select date_B from table_B" returns many records. This needs to be done thru correlated subquery which would do it for each row. Thank you all.create table table_A(keycol int,date_A date) ; create table table_B(keycol int,date_B date) ; select * from table_a 1 2004-05-01 2 2004-05-02 10 2004-05-10 select * from table_b 2 2005-05-02 1 2005-05-01 3 2005-05-03 update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol) where exists (select 1 from table_b c where a.keycol=c.keycol) select * from table_A 1 2005-05-01 2 2005-05-02 10 2004-05-10 If you exclude the exisits clause (update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol) ) select * from table_a 1 2005-05-01 2 2005-05-02 10 - Based on your requirement, you can choose one of the above statements ... Famuda, your sample even does not make really sense because there is no corrleation (join) criteria. If the challenge is just to copy all values of column B to column A you are right, but is this really the requirement? Is there a matching row in table B for every single row in table A? If you use the date as matching criteria then table A is already in the right state.
CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT NULL, `iso` varchar(3) DEFAULT NULL, `country` varchar(150) DEFAULT NULL ) ENGINE=Inno DB CREATE TABLE `country` ( `countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `ordering` smallint(5) unsigned NOT NULL DEFAULT '0', `iso` char(2) NOT NULL, PRIMARY KEY (`countryid`) ) ENGINE=Inno DB Using My SQL update multiple table syntax: 14.2.11 UPDATE Syntax Note that you have two different lengths and data types on your iso columns.These exercises allow you to try out your skills with the UPDATE statement.You will be given questions that you need to solve.In a 1: M relationship, SQL Server always uses the last row it finds for the update.However, you have no way of influencing what the last row will be, and on multiprocessor machines where the query might be parallelized, the last row might differ from execution to execution.