SQL-Update the value of a column using the value of a column of another table

Question

I have this two tables

Transactions table

TESTE TABLE

And I want to update the value of column NEW_COLUMN of table Transactions with the value of TESTE column of table TESTE if the value of SourceID is equal to the value of ID. How can I do it?

The link says to provide a minimal representation. I provided one so where is the problem? — Fabio, Jun 17 '21 at 17:21
You didn't do it in exactly the format Strawberry wants. Screenshots are generally not favored on Stack Overflow, because it's impossible to copy & paste if someone wants to test your example to make their answer. So it's preferred to provide code in text form with complete CREATE TABLE statements and INSERT sample data, to make it easy for someone to do that. Best of all use a site like https://dbfiddle.uk to make a test case. — Bill Karwin, Jun 17 '21 at 17:25
@BillKarwin I did this way because all the questions that I saw of this type provided a screenshot — Fabio, Jun 17 '21 at 17:33
Yea I provided the table format you can put in next time so that you know ... I certainly didn't know. The other thing would be to put single letter variables like 'a' instead of 'Transactions' to simplify the example. The idea I guess is to allow others to easily look at this example and help themselves too. Either way though, I didn't know this stuff either. Do what you want. You had more than enough for anyone to answer this question. These people are more or less trying to force the hand in standardizing things, but IMHO in the wrong toxic non-constructive way that pushes new people away. — Dr. No, Jun 17 '21 at 17:33
Also you can use this to write SQL queries and try them out. MariaDb Fiddle https://sqlize.online/?phpses=&sqlses=375a40e6d4bfaca2c961aeafcce8bb6a&php_version=null&sql_version=mariadb — Dr. No, Jun 17 '21 at 17:37
@Dr.No I never worked with Maria, is it much different from MySQL? — Fabio, Jun 17 '21 at 17:41
I know a lot of people post screenshots, but this is not recommended, and there are good reasons described in the Help Center: https://stackoverflow.com/help/how-to-ask — Bill Karwin, Jun 17 '21 at 17:42
MariaDB forked from MySQL in 2010, and both databases have been growing apart gradually since then. By now you should consider them as different databases, like Sybase and Microsoft SQL Server, which have a similar history. — Bill Karwin, Jun 17 '21 at 17:43
MariaDB is like open source and connected to this apache XAMPP tool that is a sort of all in one thing. MySQL is the standard what's given. There are a lot more plug-ins and such for the open source community with something like MariaDB. I believe MySQL would be more going into the paid enterprise business direction if you will. That's about the extent of my knowledge on that. Also do note, the code in MariaDB will have slightly different syntax than MySQL so beware of that too for things erroring out. — Dr. No, Jun 17 '21 at 17:43
Quite honestly, I'd follow Bill much more on this one. I'm still pretty new to it all. I just started this SQL venture because I'm building statistics for a DCS combat flight simulation game. — Dr. No, Jun 17 '21 at 17:45
For the record, MySQL is open source: https://github.com/mysql/mysql-server/blob/8.0/LICENSE with some commercial-licensed add-ons, and MariaDB has exactly the same license model: https://www.infoworld.com/article/3109213/open-source-uproar-as-mariadb-goes-commercial.html — Bill Karwin, Jun 17 '21 at 17:47
Haha well there ya go! Shows what I know, thanks Bill for the knowledge. Heck if I know. — Dr. No, Jun 17 '21 at 18:07

Dr. No · Accepted Answer · 2021-06-17T17:23:32.077

2

create table Transactions (SourceID int , NEW_COLUMN varchar(64));
insert into Transactions (SourceID, NEW_COLUMN) values 
(1, 'Default'),(2, 'Default'),(3, 'Default');

create table TESTE (ID int , TESTE varchar(64));
insert into TESTE (ID, TESTE) values 
(1, 'TESTE1'),(3, 'TESTE3');

select * from Transactions;
select * from TESTE;

    UPDATE Transactions 
    JOIN(SELECT TESTE, ID
    FROM TESTE) x ON SourceID = ID
    SET NEW_COLUMN = TESTE
    WHERE SourceID = ID;

select * from Transactions order by SourceID;


Table: Transactions

+==========+============+
| SourceID | NEW_COLUMN |
+==========+============+
| 1        | Default    |
+----------+------------+
| 2        | Default    |
+----------+------------+
| 3        | Default    |
+----------+------------+

Table: TESTE    

+====+========+
| ID | TESTE  |
+====+========+
| 1  | TESTE1 |
+----+--------+
| 3  | TESTE3 |
+----+--------+
    
    ✓

Resulting Table: Transactions 
    +==========+============+
    | SourceID | NEW_COLUMN |
    +==========+============+
    | 1        | TESTE1     |
    +----------+------------+
    | 2        | Default    |
    +----------+------------+
    | 3        | TESTE3     |
    +----------+------------+

edited Jun 17 '21 at 17:23

answered Jun 17 '21 at 16:39

Dr. No

68
7

I agree. When I was learning Prolog I made some questions that could be useful for other begginers and got a lot of downvotes – Fabio Jun 17 '21 at 16:46
So what's going on with your question is that there is UPDATE SET ... but if you have 2 tables you need to do a JOIN in order for that to work. At that point you just set the columns equal to one another and after some playing around you should be able to get it to work. – Dr. No Jun 17 '21 at 16:48
There you go, he downvoted me properly :) – Dr. No Jun 17 '21 at 17:08
FWIW, you can do this simply as a JOIN between two tables, you don't need a subquery. See an example I posted here: https://stackoverflow.com/a/2114583/20860 – Bill Karwin Jun 17 '21 at 17:21
I really don't Know the problem. I provided a small representation – Fabio Jun 17 '21 at 17:21
Whoops I had forgot a semicolon, well that's how you learn though. Thanks for the opportunity, this is the very first question that I answered =-) – Dr. No Jun 17 '21 at 17:23
I have a small doubt . Is X an alias? And why only that table as an alais? – Fabio Jun 17 '21 at 17:48
and what about if instead of SourceID and ID it was both ID? – Fabio Jun 17 '21 at 17:54
So I actually asked the same question about this x alias. The answer below is what I got on this. I'm not completely sure why you need an alias, but I believe it doesn't work without it. Here is the answer I received previously: Each DBMS have its own syntax. Sometimes the syntax of one DBMS is applicable to another, sometimes not. when you generally use correlated subquery vs. multiple-table UPDATE syntax? In update the correlated subquery should be used when the percent of the rows to be updated is not above ~5%. OF course only in simple cases and with optimal indexing. – Akina May 24 – Dr. No Jun 17 '21 at 18:01
If they were both ID then you would still set ID to ID ... however you probably need to reference both tables to each ID so that the code knows which ID you want to use. Like ... Let's say there is Table A as A and Table B as B . Then you would have to reference the IDs that are in both tables like so --> A.ID = B.ID – Dr. No Jun 17 '21 at 18:04

SQL-Update the value of a column using the value of a column of another table

Transactions table

TESTE TABLE

1 Answers1