-2

I am working on an assignment where I have to create a database using SQL Server 2019. I am wondering how to create a function which uses the existing PK of one table and puts it into the FK of the linked table. I can't upload a picture of it. But here is a description of my two tables:

Table Item:

Columns: itemID(PK), itemPackID(FK), description......

Table ItemPack:

Columns: ItemPackID(PK) ItemID(FK), quantity...

In the Item table, itemPackID as a foreign key is holding NULL and I want to replace the NULL with the PK value of the ItemPack table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben Fish
  • 1
  • What did you try? Show us what you're able to come up with so we can help you. – Biscuits Mar 26 '20 at 02:06
  • Well, I am bloody new to SQL and I just know the basics of SQL. At the moment, NZ is under lockdown and I can't go to school. So I thought it would be good to educate myself at home. I was wondering if there is a function which could do this job. I am not looking for a made answer. However, maybe can show me an example of a function in SQL where one existing value is passed to a attribute colomn holding NULL. Thanks – Ben Fish Mar 26 '20 at 02:24
  • 1
    Luckily you have access to the internet to read the documentation for an `UPDATE` statement. You'll also need to understand what a foreign key constraint is in order to get the job done. This is all Chapter 1 stuff, mate. – Biscuits Mar 26 '20 at 02:33
  • You want to UPDATE table ITEM and set a value for column `itemPackID` for rows where this column is NULL. Is this correct? – Abra Mar 26 '20 at 02:34
  • 2
    Please keep the questions and comments polite - no need for swearing. – Dale K Mar 26 '20 at 03:23
  • You can't update records in a function - you update records using an [`update` statement](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15) which is well documents with plenty of examples. – Dale K Mar 26 '20 at 03:24
  • Thanks guys. @Biscuits everybody started as a beginner, mate. Can't wait to become your boss one day. – Ben Fish Mar 26 '20 at 07:17
  • No, beginners actually try writing code. You haven't come that far. I want to help you, and this will be the next step. – Biscuits Mar 27 '20 at 18:08

1 Answers1

0

I'm considering that itemID(PK) is not null in ItemPack table. Please check the bellow code, hope this will help you.

SQL query:

UPDATE Item SET [itemPackID(FK)] = (SELECT [ItemPackID(PK)] FROM ItemPack WHERE [ItemID(FK)]=[itemID(PK)]) WHERE [itemPackID(FK)] IS NULL

Thank you

Farhana
  • 11
  • 2
  • Hello Farhana! Thank you for the answer. I think I am almost there but now it gives me the following statement: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Ben Fish Mar 26 '20 at 07:51
  • this is my code now btw: UPDATE Item SET [itemPackID] = (SELECT [itemPackID] FROM ItemPack WHERE [itemID] = [itemID]) WHERE [itemPackID] IS NULL; – Ben Fish Mar 26 '20 at 07:52
  • Are you have duplicate [ItemID(FK)] id in ItemPack table? – Farhana Mar 27 '20 at 05:56
  • yes, I have two of each item with the same Foreign Key. – Ben Fish Mar 28 '20 at 01:54
  • So if you have duplicate itemID then you got different unique id of itempack,so which id you want to update – Farhana Mar 28 '20 at 04:56
  • I am pretty sure I want to duplicate ItemPack Id in Item table to allocate each item to an itempack right? – Ben Fish Apr 02 '20 at 22:26