Set a GUID to null

[johnrg1]

Hi All,

I was wondering if anyone knows how, it at all, a guid can be removed from a table. Just the existing value, not the row.

I have a table with a guid column that relates to another table and its value is a guid. I want to set the guid to nothing if it is deleted from the other table but how?

Cheers

John

 

[checkai]

I would assume you cannot do this because the GUID is in essance a primary key, right?

dlc

 

[SQLSister]

update Table1
Set id = Null

will reset all the GUIDs to Null. So just write the appropriate update to select the records you want, use a join to the other table. Maybe something like

update Table1
Set id = Null
From Table1 left join Table2
on Table1.id = Table2.ID
where Table2.id is null

Now all this assumes that the GUID field is nullable and it is not being used as a primary key. If this is so, you would not want to set it to null.

You could also do this in a trigger using the deleted pusedotable to identify which records to set the GUID field to null. The trigger might be the best way to handle as it ensures the action will alwys happen no matter how the record is deleted from table 2.

If the GUID is a primary key in table 2, why are you keeping the rest of the record? Inthis case it would probably be better to cascade delete.