# Update Tables - relational databse

**URL:** <https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385>\
**Category:** Ignition\
**Created:** [October 5, 2020, 6:11pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385 "2020-10-05T18:11:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 5, 2020, 6:11pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/1 "2020-10-05T18:11:52Z")

</div>

Hi all,

Here again with a newbie basic question. I am sure I am making this much more complicated than it needs to be.

If I have multiple tables,…to keep it simple i’ll say 2 tables.

Users\_Table  
id\_user INTEGER  
username \<\<\<\<\<the username will be pulled from getUserName()

Other\_Data\_Table  
id\_user INTEGER \<\<\<\<\<\<\<\<\<\<related to id\_user in Users\_Table  
id\_Other\_Data INTEGER  
other\_data

I want to insert a new record. The Other\_data in the Other\_Data table and the username in the Users\_Table.

Likewise I want to SELECT the data to display as username and other data in a table.

I am thinking it will be something like:

INSERT INTO Other\_Data\_Table(id\_user, Other\_Data) VALUES (? , :data)  
WHERE id\_users = username FROM Users\_Table id\_user

Note that I will pass the user from getUserName() as a parameter but I need to get passed the relational database issue.

Other\_Data will be additional tables that I will apply the same lessons to.

Thanks for the help,

Steven

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 5, 2020, 6:19pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/2 "2020-10-05T18:19:12Z")

</div>

Hi Steven,

Read up on SQL joins, and see if that points you in the right direction. 🙂

[https://www.w3schools.com/sql/sql\_join.asp](https://www.w3schools.com/sql/sql_join.asp)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 5, 2020, 6:21pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/3 "2020-10-05T18:21:05Z")

</div>

Whoops, misread your post.

But it’s still a good thing to learn.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 5, 2020, 6:33pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/4 "2020-10-05T18:33:30Z")

</div>

Anyway, you can’t insert into two tables at the same time. I forget if you had told us what db you were using, but here’s an example on stackoverflow for MySQL it uses LAST\_INSERT\_ID to grab the id number used on the first insert:

> <https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization>

That said, if you can tell us what you’re envisioning, it may help refine the answer.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 5, 2020, 7:16pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/5 "2020-10-05T19:16:05Z")

</div>

@JordanCClark that is a LOT of information!! However, it appears I will need multiple insert statements.

My vision is to have users making notes in categories. For example, multiple Ignition Designers working on the same project, logging there work.

You would have these tables…

users\_table  
catagory\_table  
notes\_table

In order to query the notes for everyone to read, the users must be identified in a table. I learned how to get the username into a parameter to pass in a query.

However, the user may or may not exist in the table already. Same with catagory but it will be the same as the user.

So it looks like I need to query the user\_table to see if the username exist and get the id (primary key), assign it to a parameter, then use that parameter in the INSERT query into the notes\_table.

if the username does not exist, then I need to insert it, then query and assign the username parameter.

SO, what do I actually have to do?

Please keep in mind that this is a learning exercise that will end up as a project that we may or may not use at some point. I need to learn the SQL and scripting and this is a useful way for me as I learn by doing.

Thanks for the help!!

Steven

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 5, 2020, 7:32pm UTC](https://forum.inductiveautomation.com/t/update-tables-relational-databse/39385/6 "2020-10-05T19:32:47Z")

</div>

@JordanCClark I forgot to add that I am using PostgreSQL.

Thanks, Steven